Multi MySQL Queries not executing :(

20 replies
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);
?>
#executing #multi #mysql #queries
  • Profile picture of the author ussher
    the $query is not called anywhere.

    Your mysql_query() function possibly should be:
    mysql_query($query);
    Signature

    "Jamroom is a Profile Centric CMS system suitable as a development framework for building entire communities. Highly modular in concept. Suitable for enterprise level development teams or solo freelancers."

    - jamroom.net
    Download Jamroom free: Download
    {{ DiscussionBoard.errors[5152239].message }}
  • Profile picture of the author CSS Architect
    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);
    ?>
    {{ DiscussionBoard.errors[5152331].message }}
    • Profile picture of the author tampaprogrammer
      Originally Posted by CSS Architect View Post

      Actually, I did have the "mysql_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);
      ?>
      Maybe do the queries seperately?
      Look here: PHP: mysql_query - Manual
      An SQL query
      The query string should not end with a semicolon. Data inside the query should be properly escaped.


      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.
      {{ DiscussionBoard.errors[5153099].message }}
  • Profile picture of the author ussher
    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);
    ?>
    Signature

    "Jamroom is a Profile Centric CMS system suitable as a development framework for building entire communities. Highly modular in concept. Suitable for enterprise level development teams or solo freelancers."

    - jamroom.net
    Download Jamroom free: Download
    {{ DiscussionBoard.errors[5153382].message }}
  • Profile picture of the author n7 Studios
    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.
    {{ DiscussionBoard.errors[5154302].message }}
    • Profile picture of the author CSS Architect
      @n7 Studios: I removed the "@" but after running the query it just showed a blank page.

      $
      result = @mysql_query($ query);
      mysql_close($ dbc);
      ?>


      @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.
      Could not perform select query - 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 'UPDATE base SET id = 84 WHERE id = 2 && value <=19; UPDATE base ' at line 2


      @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.
      {{ DiscussionBoard.errors[5156737].message }}
  • Profile picture of the author KirkMcD
    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.
    {{ DiscussionBoard.errors[5157066].message }}
  • Profile picture of the author 2WDHost
    CSS Architect,

    Maybe this is the key :
    mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.
    Signature
    2WDH.com: Professional cPanel Hosting since 2003
    Really helpful support. You're not a number to us! Programming, database and site development help.
    No overselling. SSD. Real 99.99%+ uptime. Free 2-week trial: no obligation/credit card required.
    {{ DiscussionBoard.errors[5157364].message }}
  • Profile picture of the author CSS Architect
    @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
    {{ DiscussionBoard.errors[5158836].message }}
    • Profile picture of the author Tim Brownlaw
      Originally Posted by CSS Architect View Post

      @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
      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
      Signature

      Great Success is built from many little successes!

      http://www.timbrownlaw.com - My Wee Part of the World.

      http://www.LookingOverMyShoulder.com

      {{ DiscussionBoard.errors[5159194].message }}
      • Profile picture of the author CSS Architect
        Before
        --- ID 84 ---
        Value 10
        Value 30
        Value 50



        --- ID 2 ----

        Value 20

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

        After
        --- ID 84 ---
        Value 30
        Value 50
        Value 20
        Value 40
        Value 60


        --- ID 2 ----

        Value 10
        =======

        As you can most likely tell by now, i'm a total newbie when it comes to php/sql.

        Thanks for pointing me to the right direction. I'll check out that link.
        {{ DiscussionBoard.errors[5159278].message }}
    • Profile picture of the author 2WDHost
      Originally Posted by CSS Architect View Post

      @2WDHost: you totally lost me lol
      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?
      Signature
      2WDH.com: Professional cPanel Hosting since 2003
      Really helpful support. You're not a number to us! Programming, database and site development help.
      No overselling. SSD. Real 99.99%+ uptime. Free 2-week trial: no obligation/credit card required.
      {{ DiscussionBoard.errors[5160552].message }}
      • Profile picture of the author Tim Brownlaw
        Originally Posted by 2WDHost View Post

        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?
        @2WDHost - He has tried it - see 4 posts up.
        It is expected that single queries will work and as far as we know, there isn't any real reason not to do it that way.

        I've thrown in the multi query as a solution, but there are other little traps for the beginner...

        The question is and always will be... "What happens if an error occurs?"

        And we must ask, what is it that we are actually trying to achieve?

        We can take this slowly...

        Cheers
        Tim
        Signature

        Great Success is built from many little successes!

        http://www.timbrownlaw.com - My Wee Part of the World.

        http://www.LookingOverMyShoulder.com

        {{ DiscussionBoard.errors[5160896].message }}
        • Profile picture of the author 2WDHost
          Originally Posted by Tim Brownlaw View Post

          @2WDHost - He has tried it - see 4 posts up.
          It is expected that single queries will work and as far as we know, there isn't any real reason not to do it that way.
          CSS Architect is afraid of problems with server resource usage. So my question was not if 1 by 1 query works, but if CSS Architect noticed any troubles with server resource usage while those queries are working 1 by 1 on his server.

          As for the purpose of those UPDATE queries, that is completely different question. And I think you are doing a right thing asking that question
          Signature
          2WDH.com: Professional cPanel Hosting since 2003
          Really helpful support. You're not a number to us! Programming, database and site development help.
          No overselling. SSD. Real 99.99%+ uptime. Free 2-week trial: no obligation/credit card required.
          {{ DiscussionBoard.errors[5161143].message }}
          • Profile picture of the author Tim Brownlaw
            Originally Posted by 2WDHost View Post

            CSS Architect is afraid of problems with server resource usage. So my question was not if 1 by 1 query works, but if CSS Architect noticed any troubles with server resource usage while those queries are working 1 by 1 on his server.
            My Apologies, so you did!

            Cheers
            Tim
            Signature

            Great Success is built from many little successes!

            http://www.timbrownlaw.com - My Wee Part of the World.

            http://www.LookingOverMyShoulder.com

            {{ DiscussionBoard.errors[5161512].message }}
  • Profile picture of the author CSS Architect
    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);
    ?>
    {{ DiscussionBoard.errors[5159399].message }}
    • Profile picture of the author Tim Brownlaw
      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
      Signature

      Great Success is built from many little successes!

      http://www.timbrownlaw.com - My Wee Part of the World.

      http://www.LookingOverMyShoulder.com

      {{ DiscussionBoard.errors[5159473].message }}
  • Profile picture of the author CSS Architect
    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.
    {{ DiscussionBoard.errors[5180843].message }}
  • Profile picture of the author msam029
    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
    {{ DiscussionBoard.errors[5181170].message }}
  • Profile picture of the author rockerzz
    you cannot execute multiple queries with mysql_query
    {{ DiscussionBoard.errors[5182518].message }}

Trending Topics