CODEWITHSUNDEEP
phpmysql
Bader H Al Rayyes
Bader H Al Rayyes

Reputation: 522

Updating one mysql record each time the for loop counts in php

I'm tying to update a certain number of records , in my code here $tickets = 10 which means i need to update 10 records only in the database accroding to mysql criteria , my for loop here update all the records (100 record at once !) for my criteria , here is my code 

for ($counter = 1; $counter <= $tickets; $counter++) {

    $bookTicket = mysql_query("UPDATE units SET 
    ticketSold = 'No',
    userIdFK = '$chooseUser' 
    WHERE BusinessreservationIdFk = '$eventId' 
          AND classIDfk ='$chooseClass' ")
    or die(mysql_error());

    if ($bookTicket) 
    {
         echo "<br/>ticket " . $counter . "  done !";
    } 
    else 
    {
         echo "no<br/>";
    }                                                            
}

How can I achieve to update one record each time the for counts ?

Upvotes: 0

Views: 1186

Answers (3)

gaspercat
gaspercat

Reputation: 425

If I understand it right, you want to update 10 entries with a given businessreservationIdFk and a classIDfk, and do it so that if there are not enough free entries you don't end up with 0 reservations, but with the availible ones (for example 6 of the 10 requested).

You can do this that way:

$bookTicket = mysql_query("UPDATE units SET 
    ticketSold = 'No',
    userIdFK = '" . $chooseUser . "' 
WHERE BusinessreservationIdFk = '" . $eventId . "' AND
    classIDfk ='" . $chooseClass . "'
LIMIT 
    10
") or die(mysql_error());

if ($bookTicket){
     $num = mysql_query("SELECT COUNT(1) FROM units WHERE userIdFK = '".$chooseUser."'");
     $num = mysql_fetch_array($num)[0];
     echo $num . " tickets booked.";
}else{
     echo "Not a single ticket available!";
}

That assumes your check parameters (the ones after WHERE) identifies all the tickets not booked, but I think you have another mistake here, and only check if the tickets are from the type expected and from the bussiness expected, not if they are already booked or not. Maybe you should also check f ticketSold is in a state like "Not booked" or something.

EDIT: Note that no loop is needed

Upvotes: 1

chalet16
chalet16

Reputation: 187

You can use LIMIT in your code so your code woulde be

for ($counter = 1; $counter <= $tickets; $counter++) {

    $bookTicket = mysql_query("UPDATE units SET 
    ticketSold = 'No',
    userIdFK = '$chooseUser' 
    WHERE BusinessreservationIdFk = '$eventId' 
          AND classIDfk ='$chooseClass' LIMIT 1") //ADD LIMIT TO THIS LINE
    or die(mysql_error());

    if ($bookTicket) 
    {
         echo "<br/>ticket " . $counter . "  done !";
    } 
    else 
    {
         echo "no<br/>";
    }                                                            
}

But I think you should use this instead so you use only 1 query at one time.

$bookTicket = mysql_query("UPDATE units SET 
ticketSold = 'No',
userIdFK = '$chooseUser' 
WHERE BusinessreservationIdFk = '$eventId' 
      AND classIDfk ='$chooseClass' LIMIT 10")

PS. I think your code would be risk to SQL injection security problem. Please look about it too.

Upvotes: 1

KevInSol
KevInSol

Reputation: 2610

Without seeing more of the context of both the PHP code and the database contents, it looks like this code will basically run the same SQL 10 times. I don't see where $eventId or $chooseClass changes each time the loop executes. And, if there are more than 1 (ot a 100 as it seems) matching records (for the combination of $eventId and $chooseClass) in your data set the above code will update more than one record per loop.

You need to look at it from am SQL point of view first, ask what you want to update and see what SQL would do that, then forumlate a loop that updates one record at a time.

Or, are you trying to just mark off 10 out of a possible tickets as sold or not?

Then one SQL statement like update table set sold =no limit 10 should do it. (untested psudo code)

Upvotes: 0

Related Questions