Forum Moderators: coopster
To make my network more efficiency, i want to create a stored procedures by using MYSQL Browser. I google many articles but still don't know how to write the procedure that describe conditions and make it works on my php script.
The question still same as my previous post:
when a user select 'Audi' from the list, it will record the submission date and return to the form. The drop down list will then populate available car brand that has not yet selected for following user.
Here is MYSQL Table:
----------------------------
id : car_brand : date_check
----------------------------
1 : Audi : NULL
2 : Mazda : NULL
3 : Toyota: NULL
-----------------------------
Here is my php script:
<?php
if ( isset($_POST['Submit']) ){
$link = mysql_connect("localhost", "user", "pwd") or die("Connection cannot establish");
$query = "UPDATE auto_table SET WHERE id = '".((int)$_POST['Car'])."' LIMIT 1";
mysql_query($query,$link) or die("Query Failed");
echo '<script>alert("Update Successfully !");opener.location.reload();</script>';
}
$link = mysql_connect("localhost", "user", "pwd") or die("Connection cannot establish");
$query = "SELECT id,car_brand FROM auto_table WHERE date_check IS NULL ORDER BY id ASC";
$result = mysql_query($query,$link) or die("Query Failed");
$options = array();
while($row = mysql_fetch_array($result)){
$options[] = $row;
}
?>
<body topmargin="20" leftmargin="20">
<table width="367" border="1" cellpadding="0" cellspacing="1" bordercolor="#333333" align="center">
<form method="post">
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="1">
<tr>
<td width="91"><strong>Car</strong></td>
<td width="266">:
<select name='Car'>
<?php foreach($options as $data){ ?>
<option value='<?php echo $data['id']; ?>'><?php echo $data['car_brand']; ?></option>
<?php } ?>
</select>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" name="Submit" value="Update">
</td>
</tr>
</table>
</td>
</tr>
</form>
</table>
</body>
I believe this is some advance level question, does anyone here can help me out?Many thanks
If both of those numbers are relatively low, there is no real point in using stored procedures as good indexing will make it fast enough and there will be no noticable difference.
As your question, everytime a user select a type of car in the list, it will updated the selection time in database. And then, the form will return and the selected type of car will no longer available for following user. It will keep on looping until there is no available car in dropdown list. The table only for a simulation, as i'm not going to list down 100 car brand and with some other important fieldnames. If someone here has knowledge regarding Stored Procedure, I hope there is some simple guide for me. Many thanks.
I found the following for you. This is pure speculation, not a clue if it will actually work as I haven't used store procedures in eons.
To create a stored procedure, you run an SQL statement like the following, (this is untested)
CREATE PROCEDURE carsearch(param_carid int)
MODIFIES SQL DATA
BEGIN
UPDATE cartable(
SET datecheck = NOW()
WHERE id = param_carid
END;Then to run this in PHP, do something like,
$sth = $mysqli->prepare("CALL carsearch(?)");
$sth->bind_param('i',$id);
$sth->execute();
Now, i solved my question but just need to know how to combine 2 SP into one so i dun have to call up 2 SP in my php. Here is my complete work in stored procedure:
1)code_select:
DELIMITER $$
DROP PROCEDURE IF EXISTS `auto_table`.`code_select` $$
CREATE DEFINER=`root`@`%` PROCEDURE `code_select`()
BEGIN
SELECT id,car_brand FROM auto_brand where date_check is null;
END $$
DELIMITER ;
2)code_update:
DELIMITER $$
DROP PROCEDURE IF EXISTS `auto_table`.`code_update` $$
CREATE PROCEDURE `auto_table`.`code_update` (
IN in_id bigint,
IN in_car_brand varchar(30)
)
BEGIN
update auto_table set date_check = now() where id = in_id limit 1;
END $$
DELIMITER ;
As in php script, just replace select mysql query with call auto_brand.code_select() and replace update statement with call auto_brand.code_update( any neccessary pre-define variables to pass to store procedure parameters ).
If anyone can tell me how to call one SP instead of 2, that would b very helpful. Many thanks :)
