Multiple update query

webAmeteur

Hi there.

I want to update my database using PHP. I have a table called 'screens' with rows 1 to 500. Each row has an attribute, seat_status which is either 'BOOKED' or 'EMPTY'.

On my site, I have 10 hiddenFields, some of which will have an INT value between 1 and 500.

The Update I want is as follows:

UPDATE screens SET seat_status='BOOKED' WHERE seat_number = [the value of hiddenField0] OR [the value of hiddenField1] OR etc...

I have got this code so far, but it only updates the first seat_number:

$updateSQL = sprintf("UPDATE screens SET seat_status='BOOKED' WHERE seat_number=%s", GetSQLValueString($POST['hiddenField0'], "int"),
"OR seat_number=%s", GetSQLValueString($POST['hiddenField1'], "int"), OR etc...

Any ideas on how I could make it so it updates all the rows would be appreciated

Regards

Roger_Ramjet

Replace the commas with full stops for the concat to work.

Next time echo out the sql before you run it and you would have seen the other parts had not been added to the query.

webAmeteur

I changed the commas to full stops, hoiwever now it returns The Query is Empty.

If i left the comma in between strings i.e.

$updateSQL = sprintf("UPDATE screens SET seat_status='BOOKED' WHERE seat_number=%s". GetSQLValueString($POST['hiddenField0']. "int"),
"OR seat_number=%s". GetSQLValueString($POST['hiddenField1']. "int")

It return an SQL error near

'OR seat_number=%s14int13int'

Regards

Roger_Ramjet

Wrong commas. You're going backwards Webbie.


$updateSQL = "UPDATE screens SET seat_status='BOOKED' WHERE seat_number=%s". GetSQLValueString($_POST['hiddenField0'], "int").
"OR seat_number=%s". GetSQLValueString($_POST['hiddenField1'], "int")

webAmeteur

Going backwards - us new people to this hey.

Ok so I tried changing that and now I get this

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '%s46OR seat_number=%s47' at line 1

so i removed the %s in the code (hought that may do it) and then I got this

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'seat_number=47' at line 1

The seat_numbers I tried to book were 46 and 47.

Regards

Roger_Ramjet

Missing a gap between 46 and OR in the first line. Sorry, my fault, missing from what I posted. But then read my by-line. You should always check the code I post for typos. I assumed that you were generating this in a loop for the number of seats posted so mine was just for explanation.

I've never seen this GetSQLValueString before. Seems a bit of overkill for a simple - embed an integer into a query situation.

webAmeteur

Probably very cheeky here to ask, but If you were doing this site, how would you code it, because I am getting very confused at what I have done here.

10 hiddenfields all with an integer in, and then using those integers in an update.

Many thanks

Roger_Ramjet

Well, the idea (I forget whose) of storing the seat nos selected as a comma delimited string in a text or hidden field was a good one, I'd use hidden. When the update script gets them, explode() the string into an array on the commas and then use foreach to add the 'OR seatno=i' to the base query. Since they are integers there is very little to worry about, no need to escape anything.

I would, on reflection, also add " AND seat_status='empty' " to the where clause to stop double bookings. I would then select them back using the booking ID to confirm that they had been booked and report this back to the user: if the successfull booking did not match the user input then report this back and represent the diagram for them to select alternates.

I'm a firm believer in keeping everything as simple as possible: it's called the KISS principle. Back around the 10th century an Arabian mathematician formulated a test for competing proofs for the same maths theory. He said that the proof that required the invention of the fewest new entities was the right one: Occam's Razor. In mechanical engineering it is the design with the fewest moving parts that is deamed the best, less things to go wrong. And I believe this applies to programming as well, the simpler the algorithm the less ther is to go wrong.

webAmeteur

Ok Ill give it a go. I like the idea of keeping it simple. I like the idea, but probally easier said than done!

Regards

webAmeteur

OK, here is my attempt. Got the explode properties of PHP.net.

$list1 = ($POST['list1']);
$sepFields = explode(",", $list1);
if ((isset($POST["MM_update"])) && ($_POST["MM_update"] == "form2")) {
$updateSQL = sprintf("UPDATE screens SET seat_status='BOOKED' WHERE seat_number=" .$sepFields[0]. "OR seat_number="
.$sepFields[1]. "OR seat_number="
.$sepFields[2]. "OR seat_number="
.$sepFields[3]. "OR seat_number="
.$sepFields[4]. "OR seat_number="
.$sepFields[5]. "OR seat_number="
.$sepFields[6]. "OR seat_number="
.$sepFields[7]. "OR seat_number="
.$sepFields[8]. "OR seat_number="
.$sepFields[9]. "OR seat_number=");

mysql_select_db($database_conn1, $conn1);
$Result1 = mysql_query($updateSQL, $conn1) or die(mysql_error());

$updateGoTo = "cinema_admin.php";
if (isset($SERVER['QUERY_STRING'])) {
$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
$updateGoTo .= $SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $updateGoTo));
}

list1 is the textbox that holds the values seperated by commas.

It brings up error

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR seat_number=OR seat_number=OR seat_number=OR seat_number=OR

I have tried to echo out the results in sepFields[0] etc... but it displays nothing

Thanks for your help

Roger_Ramjet

THIS is how you generate a variable query


// assuming you have already tested for the presense of a list
$seat_ary = explode(',' , $_POST['list1']);

// set up a counter
$i = 1;

foreach ($seat_ary as $val) {
	// first time in so set up query base
	if ($i == 1) { $sql = "UPDATE seats SET seat_status='Booked' WHERE (seat_number=$val "; }
	// after that just add the next seat no
	$sql .= " OR seat_number=$val ";
	$i++
}
 // now finish off the query
$sql .= ") AND seat_status='Empty' ":

Your main problem seems to be forgetting to leave spaces between things, which make the string unintelligeable to mysql. Where does a field name end and a keyword begin if ther is no space between them?

webAmeteur

Thanks for your patience with me.

When running the code it seems to print the last value in seat_ary twice
e.g.

UPDATE seats SET seat_status='Booked' WHERE (seat_number=34 OR seat_number=34 ) AND seat_status='Empty'

I put some test data in to explode $test = ("10,23,43,34");

I then thought maybe that the $sql wasn't being updates so I did

$sql = $sql .= " OR seat_number=$val ";

for all the sdtatement updating the query.

This however didnt make any difference

Many thanks for your help

Roger_Ramjet

Whoops, forgot the else. What happens when I'm bashing it out. But you should have spotted it for yourself. See if you can work this one out yourself.

I'm going to change my by-line to 'my code intentionally contains bugs for you to fix so that you learn something, which is what 'paste my code at your peril' means.

webAmeteur

Ok, ive gone through your code, letter by letter, number by number. It now echo's only out 1 value

i.e.
UPDATE screens SET seat_status='Booked' WHERE (seat_number=45) AND seat_status='Empty'

so I though maybe i needed to i++ after the (UPDATE screens SET ...

e.g.

$test = ("12,23,34,45");
$seat_ary = explode(',' , $test);

// set up a counter
$i = 1;

foreach ($seat_ary as $val) {

if ($i = 1) {
$updateSQL = "UPDATE screens SET seat_status='Booked' WHERE (seat_number=$val";
$i++;
}
// after that just add the next seat no
else {
$updateSQL = $updateSQL .= " OR seat_number=$val ";
$i++;
}
}
$updateSQL = $updateSQL .= ") AND seat_status='Empty' ";

echo $updateSQL;

and again it only updates the one seat_number (the last one).

Im sorry if this is getting on your nerves

Many thanks

Roger_Ramjet

I wish you'd use the php tags


<!-- Start Find Portfolio Block -->
$test = ("12,23,34,45");
$seat_ary = explode(',' , $test);

// set up a counter
$i = 1;

foreach ($seat_ary as $val) {
   if ($i == 1) {
   	  // first time through so set up base query
      $updateSQL = "UPDATE screens SET seat_status='Booked' WHERE (seat_number=$val";
   }
     // after that just add the next seat no
   else {
      $updateSQL .= " OR seat_number=$val ";
   }
   $i++;
}

$updateSQL .= ") AND seat_status='Empty' ";

echo $updateSQL;

And stop messing with the code. Compare this to what I first posted and what you then posted. All you had to do was add the else { .... } and it would have worked. the .= syntax is just shorthand for add this to the end of me.

What I posted with the else added:

// assuming you have already tested for the presense of a list
$seat_ary = explode(',' , $_POST['list1']);

// set up a counter
$i = 1;

foreach ($seat_ary as $val) {
    // first time in so set up query base
    if ($i == 1) { $sql = "UPDATE seats SET seat_status='Booked' WHERE (seat_number=$val "; }
    // after that just add the next seat no
    else {
    $sql .= " OR seat_number=$val ";
    }
    $i++
}
 // now finish off the query
$sql .= ") AND seat_status='Empty' ":

webAmeteur

Its all working now.

Just wanna say thanks very much for being patient.

Couldnt of done it without you.

Kind Regards