Web Analytics Made Easy -
StatCounter mysqli_multiple_query issue when using foreach for an array - CodingForum

Announcement

Collapse
No announcement yet.

mysqli_multiple_query issue when using foreach for an array

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysqli_multiple_query issue when using foreach for an array

    Hi All

    I have the below code which works fine and updates each record contained in the array:

    PHP Code:
    $check_list = isset($_POST['check_list']) ? $_POST['check_list'] : array();  
    foreach(
    $check_list as $check_list) {
    $query "UPDATE `stock` SET `signature_id` = 0, 
                                  user_id = 0, 
                                 `status_id` = 1 
                WHERE `id` = '
    $check_list'";
    $result mysqli_query($conn$query); 

    I now need it to execute multiple queries for each result in the array so I have changed the code to the following using mysqli_multi_query:

    PHP Code:
    $check_list = isset($_POST['check_list']) ? $_POST['check_list'] : array();  
    foreach(
    $check_list as $check_list) {
        
    $query "UPDATE `stock` SET `signature_id` = 0, 
                                      user_id = 0, 
                                     `status_id` = 1 
                    WHERE `id` = '
    $check_list';
                   INSERT INTO `returned`
                            (`id`, `stock_id`, `signature_id`, 
                            `user_id`, `timestamp`) 
                    VALUES ('','
    $check_list','$id',
                            '
    $user_id',now())";
        
    $result mysqli_multi_query($conn$query); 
    But it now only executes one UPDATE and one INSERT for the first record in the array, and ignores the others

    I am unsure of any other way I could do this, please can anyone assist?

    Many thanks Jon

  • #2
    First off if you're going to use mysqli, USE IT. What do I mean by that? PREPARED QUERIES. This is 2016, you don't blindly paste values from variables into your query strings like you are still slopping along using the old and now defunct mysql_ functions.

    To that end, what would be optimal here is a techinique called POEM. Prepare Once, Execute Mostly. You PREPARE before the loop, and execute multiple times inside it.

    Second, I've found that multiple queries inside one query string are... unreliable at best. Particularly with prepare/execute... they don't even EXIST as an OPTION if using PDO, and with mysqli they're pretty hit or miss so I'd suck it up, and break that into separate queries.

    Third, an ID should probably be an auto increment auto-fill, so why waste time saying it in your insert? If anything that would/should/could BREAK the INSERT depending on the SQL engine.

    Fourth, if the array is empty, why in blazes would you run ANY of that code by creating an empty array? Waste of time and processing! I'd get a LOT more robust checks on that one.

    Finally, you're foreach is busted nonsense... if you foreach ($check_list as $check_list) it's overwriting itself! See the problem with that?

    I would probably also not waste time on a extra variable for nothing in there on the post value.

    Code:
    if (
    	isset($_POST['check_list']) &&
    	is_array($_POST['check_list']) &&
    	count($_POST['check_list'])
    ) {
    	$update = $conn->prepare('
    		UPDATE stock
    		SET
    			signature_id = 0,
    			user_id = 0,
    			status_id = 1
    		WHERE id = ?
    	');
    	$update->bind_param('i', $check_item);
    	$insert = $conn->prepare('
    		INSERT INTO returned (
    			stock_id, signature_id, user_id, `timestamp`
    		) values (
    			?, ?, ?, now()
    		)
    	');
    	$insert->bind_param('iii', $check_item, $id, $user_id);
    	foreach ($_POST['check_list'] as $check_item) {
    		$update->execute();
    		$insert->execute();
    	}
    } else {
    	// handle empty $_POST['check_list'] here
    }
    WAY simpler, and would run way faster since none of that string addition nonsense is needed inside the loop. That's why we have prepare, and part of why mysql_ functions went the way of the dodo. (or at least are supposed to).

    -- edit -- Oh, I also assumed that all those items and id's are integers on the bind_param.
    Last edited by deathshadow; Oct 7, 2016, 03:02 AM.
    Walk the dark path, sleep with angels, call the past for help.
    https://cutcodedown.com
    https://medium.com/@deathshadow

    Comment

    Working...
    X