mySql logical operators not working

JosiahBradley

[H]ard|Gawd
Joined
Mar 19, 2006
Messages
1,791
In PHPmyAdmin when I run the following line
Code:
SELECT product.ProductID FROM product WHERE WineID=  1 && CaseQuantity=6 && BottleUsedID=1
I am happily given the result which is 1
However when I execute this sql query in php
PHP:
$sql = "SELECT product.ProductID FROM product WHERE WineID= ". $WineID ." && CaseQuantity=". $_SESSION['Case_Selected'] ." && BottleUsedID=". $_SESSION['Bottle_Selected'];
where the session varibles are correctly set to 1 and 6 and 1, I recieve the following error:
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 '&& CaseQuantity=6 && BottleUsedID=1' at line 1
I have tried using AND and get the same error. As you can tell the error message contains the correct values 6 and 1.
If I run a query with only one thing in the WHERE cluase it executes fine, but I need the other values to be able to select my ProductID.
 
Have it echo the whole query so we can see what it's doing there.

Also, escaping the data, or at least putting them in quotes, usually makes it more readable and more separated to avoid precedence problems, such as in the following:
PHP:
$sql = "SELECT product.ProductID FROM product WHERE WineID='". $WineID ."' && CaseQuantity='". $_SESSION['Case_Selected'] ."' && BottleUsedID='". $_SESSION['Bottle_Selected']."'";
which gives us
Code:
SELECT product.ProductID FROM product WHERE WineID='1' && CaseQuantity='6' && BottleUsedID='1'
 
Putting data in quotes does nothing for precedence issues; you're thinking of parenthesis. Quoting numerical data may force the database to do an implicit conversion, and perhaps even miss an index.

At the time the query fails, what is the content of $WineID ?
 
Put the query directly into mysql_query(). First putting it into a separate variable is a disease spread by newbies to newbies. And you don't need to use product.ProductID when you are just querying a single table. You people need some lessons in efficient and scalable programming.

And the error is NOT due to the logical operators not working. :rolleyes:
The most likely scenario is that the first variable is simply EMPTY, as mikeblas has already pointed out.
 
How does scoping the table name cause scalability problems or reduce efficiency?
 
At the time the query fails, what is the content of $WineID ?
1

The most likely scenario is that the first variable is simply EMPTY, as mikeblas has already pointed out.

Thanks for the advice, I am a noob this is my first time using php/mySQL and doing anything related to with the web.

You guys were right. I forgot to put the value of $WineID into my session variable and so the value $WineID never existed because I am recalling the page several times.

Now it works perfectly with $_SESSION['WineID']

I'll make sure to remove all my $sql strings as well.
 
Put the query directly into mysql_query(). First putting it into a separate variable is a disease spread by newbies to newbies.

Because he said so and it sounded like it made sense.
 
It's probably better to keep the sql statement in a variable for 2 reasons. It's easier to print out the statements when you are debugging and you'll have to start doing it when you build more complex queries ie. adding or removing conditionals based on how the user inputs data.

Your time would be better spent creating a wrapper class for mysql_query(). If you decide to change your db to a different vendor at a later date, you don't have to hunt down every call to mysql_query. You could also add code to escape the queries to prevent sql injections.
 
How does scoping the table name cause scalability problems or reduce efficiency?
By itself, it doesn't. But efficient programming is all about removing EVERYTHING that's not absolutely necessary.

It doesn't--but I guess you're the guy who's doing the work.
Just because it doesn't make sense to you doesn't mean there's no point to it. First putting the query in a variable and THEN putting it into mysql_query() is a wasted processor cycle, simple as that.

I realize that you don't care about efficient programming since your website only receives ten page views a year, but once you get into tens of millions of page views a month, you do care about EVERY LITTLE BIT.
 
By itself, it doesn't. But efficient programming is all about removing EVERYTHING that's not absolutely necessary.
Then, why not advise JosiahBradley to write this in assembly language?

Just because it doesn't make sense to you doesn't mean there's no point to it.
I never said there wasn't any point to it, tough guy. In fact, I said quite the opposite: if it makes sense to the person doing the work, by all means, he should go ahead.

First putting the query in a variable and THEN putting it into mysql_query() is a wasted processor cycle, simple as that.
Oh, it's more than one processor cycle. It could be a few hundred. Let's say, in fact, it's 2200. Maybe, on a 2.2 Gigahertz machine, that amounts to a millionth of a second.

If you save a millionth of a second, then what?

Meanwhile, say the code is easier to debug if the SQL was in a variable. Or, easier to maintain, or easier to enhance. Let's say it saves the next developer three minutes. If that developer is making $50,000 per year, he's making about $25 per hour. Or, in three minutes, he's making 42 cents in that three minutes.

Let me ask you something. What's worth more? A millionth of a second in CPU time per hit, or 42 cents per developer, per visit to this code? sauzai figured out the right answer.

I realize that you don't care about efficient programming since your website only receives ten page views a year, but once you get into tens of millions of page views a month, you do care about EVERY LITTLE BIT.
You're really barking up the wrong tree now.
 
I am glad the discussion is getting technical but please don't start arguing OK. I take new things I learn into consideration, and see what works for me. The project I am working on is not for a business or anything, just something I am doing for my database class, and I had gotten stuck at that query. I am now finished with the assignment and I believe I got an A, so everything is OK.
Then, why not advise JosiahBradley to write this in assembly language?

I would actually enjoy doing that, but I am still fresh with assembly. And most people probably don't want to see their websites displayed at VGA resolution and have to download an interface to make it work. Not sure how to connect a program running in assembly to allow it to display on a web page anyway or any network at that level. I just enjoy witting math and graphics programs.
 
And most people probably don't want to see their websites displayed at VGA resolution and have to download an interface to make it work. Not sure how to connect a program running in assembly to allow it to display on a web page anyway or any network at that level. I just enjoy witting math and graphics programs.
Your PHP script runs on the server. The web server starts up PHP, PHP runs your script, and dumps its output to the web page. PHP shuts down, then the web server waits around for another request.

PHP is an extension to the web server. You can write such an extension in practically any language you'd like; nothing has to be downloaded to the client, and it doesn't really matter what resolution the client uses to display the site.
 
Back
Top