Multi MySQL Queries not executing :(

14 years ago

the $query is not called anywhere.

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

Thanks

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 }}

CSS Architect

14 years ago

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);
?>

Thanks
1 reply

{{ DiscussionBoard.errors[5152331].message }}

tampaprogrammer

14 years ago

Originally Posted by CSS Architect

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.

[ 1 ] Thanks

{{ DiscussionBoard.errors[5153099].message }}

ussher

14 years ago

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

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 }}

n7 Studios

14 years ago

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.

Thanks
1 reply

Signature

THE Web Development Guru - Bespoke WordPress Theme, Plugin + iPhone Apps [HTML5, CSS3, PHP, MySQL].

{{ DiscussionBoard.errors[5154302].message }}

CSS Architect

14 years ago

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

Thanks

{{ DiscussionBoard.errors[5156737].message }}

KirkMcD

14 years ago

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

{{ DiscussionBoard.errors[5157066].message }}

2WDHost

14 years ago

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.

Thanks

{{ DiscussionBoard.errors[5157364].message }}

CSS Architect

14 years ago

@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

Thanks
2 replies

{{ DiscussionBoard.errors[5158836].message }}

Tim Brownlaw

14 years ago

Originally Posted by 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

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

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 }}

CSS Architect 14 years ago

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.
- Thanks
{{ DiscussionBoard.errors[5159278].message }}

2WDHost

14 years ago

Originally Posted by CSS Architect

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

[ 1 ] Thanks
1 reply

{{ DiscussionBoard.errors[5160552].message }}

Tim Brownlaw

14 years ago

Originally Posted by 2WDHost

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

Thanks
1 reply

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 }}

2WDHost

14 years ago

Originally Posted by Tim Brownlaw

@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

Thanks
1 reply

{{ DiscussionBoard.errors[5161143].message }}

Tim Brownlaw

14 years ago

Originally Posted by 2WDHost

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

Thanks

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 }}

CSS Architect

14 years ago

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);
?>

Thanks
1 reply

{{ DiscussionBoard.errors[5159399].message }}

Tim Brownlaw 14 years ago

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
- Thanks
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 }}

CSS Architect

14 years ago

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.

Thanks

{{ DiscussionBoard.errors[5180843].message }}

msam029

14 years ago

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

Thanks

{{ DiscussionBoard.errors[5181170].message }}

rockerzz

14 years ago

you cannot execute multiple queries with mysql_query

Thanks

{{ DiscussionBoard.errors[5182518].message }}

Multi MySQL Queries not executing :(

Trending Topics

Clickbank, Digistore24 or Amazon Associates

Buying passive-income KDP businesses on Flippa

What is the fastest way to get traffic to an affiliate link?

Shopify + Syncee for dropshipping. Is this a good combination?

Hey Warriors! Excited to join the community