Multi MySQL Queries not executing :(

by 20 replies
24
I'm trying to have this executed from a php page, but it doesn't seem to make any changes.

When I run this query directly from phpMyAdmin, it works perfectly, but when trying to process it from a URL as a php file, it doesn't update.

I know there no issues with connecting to the database (db-connect.php) because I use the same file for other single query without any issues.

What am I doing wrong?

PHP Code:
<?php
require_once('/home/db-connect.php'); // connect to db

$ query = "
UPDATE base SET id = 84 WHERE id = 2 && value <=19;
UPDATE base SET id = 2 WHERE id = 84 && value >=20;
UPDATE base SET id = 78 WHERE id = 18 && value <=19;
UPDATE base SET id = 18 WHERE id = 78 && value >=20;
UPDATE base SET id = 79 WHERE id = 46 && value <=19;
UPDATE base SET id = 46 WHERE id = 79 && value >=20;
UPDATE base SET id = 80 WHERE id = 12 && value <=19;
UPDATE base SET id = 12 WHERE id = 80 && value >=20";

$ result = @mysql_query($ query);
mysql_close($ dbc);
?>
#programming #executing #multi #mysql #queries
  • the $query is not called anywhere.

    Your mysql_query() function possibly should be:
    mysql_query($query);
  • Actually, I did have the "mysql_query($query);" in there, but forgot to add the space "$ query" like I did for the top.

    For some reason it hides anything with $ when using the BB code [php] [/ php]
    --------
    So, basically, i already have "mysql_query();" in there. Any other ideas what might be causing the problem?

    This is what I have (minus the spaces after $)


    PHP Code:
    <?php
    require_once('/home/db-connect.php'); // connect to db

    $ query = "
    UPDATE base SET id = 84 WHERE id = 2 && value <=19;
    UPDATE base SET id = 2 WHERE id = 84 && value >=20;
    UPDATE base SET id = 78 WHERE id = 18 && value <=19;
    UPDATE base SET id = 18 WHERE id = 78 && value >=20;
    UPDATE base SET id = 79 WHERE id = 46 && value <=19;
    UPDATE base SET id = 46 WHERE id = 79 && value >=20;
    UPDATE base SET id = 80 WHERE id = 12 && value <=19;
    UPDATE base SET id = 12 WHERE id = 80 && value >=20"    ;

    $     result = @mysql_query($ query);
    mysql_close($ dbc);
    ?>
    • [1] reply
    • Maybe do the queries seperately?
      Look here: PHP: mysql_query - Manual


      If in a pinch, this will work for sure:
      mysql_query("UPDATE base SET id = 84 WHERE id = 2 && value <=19");
      mysql_query("UPDATE base SET id = 2 WHERE id = 84 && value >=20");

      and so on...
      You don't need "result" to be actually loaded with data do you?

      Of course, you could create an easy loop to run the query 8 times as well but if that is all you need above and is static, that would be an easy fix above.
      • [ 1 ] Thanks
  • missing a semicolon after that last update line, might make a difference, PhpStorm IDE is complaining about it.

    Try this: (yeah the forums code blocks don't work, amazing in a programming-talk forum, but hey.)
    <?php
    require_once('/home/db-connect.php'); // connect to db

    $query = "
    UPDATE base SET id = 84 WHERE id = 2 && value <=19;
    UPDATE base SET id = 2 WHERE id = 84 && value >=20;
    UPDATE base SET id = 78 WHERE id = 18 && value <=19;
    UPDATE base SET id = 18 WHERE id = 78 && value >=20;
    UPDATE base SET id = 79 WHERE id = 46 && value <=19;
    UPDATE base SET id = 46 WHERE id = 79 && value >=20;
    UPDATE base SET id = 80 WHERE id = 12 && value <=19;
    UPDATE base SET id = 12 WHERE id = 80 && value >=20
    ";

    $result = mysql_query($query) or die("Could not perform select query - " . mysql_error());
    mysql_close($dbc);
    ?>
    • [ 1 ] Thanks
  • Change @mysql_query to mysql_query and see what output you get from the query.

    If it's not updating the database because of an error in your query statement, it'll then tell you.

    Otherwise you'll need to step through your code and other files to make sure that what you have there is actually being called.
    • [1] reply
    • @n7 Studios: I removed the "@" but after running the query it just showed a blank page.


      @ussher: I made the changes as you suggested and it came out with this error message. However, I don't see any problems with this though. The command looks correct to me.


      @tampaprogrammer: I thought about doing that, but I actually have over 50 queries, so I was worried that it would kill the server resource. I'm not sure if it would, but I'm just assuming.
  • Instead of this:
    $query = "
    UPDATE base SET id = 84 WHERE id = 2 && value <=19;

    Try this:
    $query = "UPDATE base SET id = 84 WHERE id = 2 && value <=19;

    Also place the closing quote on the same line as the last update statement.
    • [ 1 ] Thanks
  • CSS Architect,

    Maybe this is the key :
  • @KirkMcD: do you mean like this?

    mysql_query("UPDATE base SET id = 84 WHERE id = 2 && value <=19");
    mysql_query("UPDATE base SET id = 2 WHERE id = 84 && value >=20");

    I have a lot more query then listed on here, so I'm not too sure if it will drain the server resource.




    @2WDHost: you totally lost me lol
    • [2] replies
    • Are these SQL statements just example you've created or are they what you actually want to run?

      For instance, If you end up setting id 84 to 2, why do you test to see if you need to change 2 back to 84?

      The condition is unlikey to have changed!

      You could perform a select on all the ids whose value are <=19 and peform the appropriate updates and the same for those >=20.

      From the select results, you'd loop creating the SQL statement for each ID.

      Just my 2 cents worth without having any real clue as to what you are actually trying to achieve...

      When you do have issues, it's always good to fallback to the simple as Kirk has pointed out.

      2WDHost is pointing out that mysql_query doesnt like having to digest more than one statement at a time...

      As is mentioned at .... PHP: mysql_query - Manual

      You can use mysqli_multi_query which you can find at ... PHP: mysqli::multi_query - Manual

      Now there's some homework for you

      But I'd still go back to "what am I really trying to do" and see if there is another way you could be doing this.

      Tis all fun

      Cheers
      Tim
      • [ 1 ] Thanks
      • [1] reply
    • I just pointed out the real reason of the discussed issue.

      You can use mysqli with mysqli_multi_query, but I'm not sure that is necessary i your case. Do you experience any troubles running 50+ UPDATE queries 1 by 1 currently?
      • [ 1 ] Thanks
      • [1] reply
  • I tired to do this, but not sure where i went wrong.

    -------------------------

    <?php
    require_once('/home/db-connect.php'); // connect to db

    $query = "UPDATE base SET id = 84 WHERE id = 2 && value <=19;"
    $query =. "UPDATE base SET id = 2 WHERE id = 84 && value >=20;"
    $query =. "UPDATE base SET id = 78 WHERE id = 18 && value <=19;"
    $query =. "UPDATE base SET id = 18 WHERE id = 78 && value >=20;"

    mysqli_multi_query($dbc, $query); // execute statements

    $result = mysql_query($query) or die("Could not perform select query - " . mysql_error());
    mysql_close($dbc);
    ?>
    • [1] reply
    • You didn't do your homework!

      mysqli is an extension to mysql, so you need to see if it's available (it should be - I'm not sure what you are running on ie - a live server or a local one on your PC like WAMP) ... then you need to perform a different connect instruction from the one you are using... in fact you don't use any mysql_... from now on...

      So look up mysqli , connecting using mysqli and all that good stuff on Google... php.net is a good place to look, especially the examples.

      It's better that you look it up and have a go first. It'll help skyrocket you from being a newbie in no time...

      Shout if you are still stuck!

      Just to add to this... whenever you discover/find a new instuction, read up on it...

      Cheers
      Tim
  • I wasn't able to figure it out and end up just doing:

    mysql_query("UPDATE base SET id = 84 WHERE id = 2 && value <=19");
    mysql_query("UPDATE base SET id = 2 WHERE id = 84 && value >=20");

    I don't have enough time this week to study up on this, going on a cruise on the 10th and I still have a lot of freelance project to finish up with (not PHP/MySQL based project of course :p ) , but I'll come back to this later on.
  • Good luck But need to have the basics right other wise you will get into issues likes this .
    Php is not rocket science but need some basics correct.
    You can do it man
  • you cannot execute multiple queries with mysql_query
  • [DELETED]
Join the discussion

Next Topics on Trending Feed