Simple query string syntax problems

Fife_Club

Hi. I'm just beginning to learn this stuff and I've got a few simple questions.

1) I'm using this very simple code to filter only those results where classyear = 1994:

$query = "SELECT * FROM alumni WHERE classyear=$classyear";
$result = mysql_query ($query);

and it would be called by this in the address bar:

file.php?classyear=1994

This all works, but how do I query multiple years? Either "1990, 1991, 1992, 1993, 1994" or "1990 through 1994"? Thanks!

2) The code above works fine with one value so I figured it would be easy to alter this code to make another page to filter only those results where state = NY:

$query = "SELECT * FROM alumni WHERE state=$state";
$result = mysql_query ($query);

and it would be called by this in the address bar:

file.php?state=NY

But this does not work at all. I should have a hundred results where the value of the field "state" is "NY", but it always gives me "Sorry, no records were found!" This isn't logical to me but I'm new to this. Why does this 'style of' code work for one field but not for another? What should I do? Thanks again?

(let me know if you need the entire code or the example URL's)

eoghain

The reason this 'style' of code works for one query and not the other is that in your first query you are searching for an integer value and in the second you are searching for a string. To make the second one work just put quotes around your variable and it should work. Depending on your database you might have to use specifically single or double quotes.

As for useing multiple years/states you will need to construct your query a bit differently. First build a comma separated list of the years/states that you want to use in the query (don't forget the quotes around each state i.e "NY", "CA"). Then put that variable into a query that looks like this:

SELECT *
FROM alumni
WHERE classyear IN ($list);

The IN keyword lets you pass in a comma separated list that will be used in the query like a list of OR values:

SELECT *
FROM alumni
WHERE classyear = 1994
OR classyear = 1995
OR classyear = 1996

I'm sure you can see how the first example is more readable and easier to used.

Fife_Club

Awesome. Thanks!

I love it when the answers are easy enough that I understand 😃

barand

Where you have a continuous range, as with your years 1990 thru 1994 , I find it's easier to specify

$firstyear = 1990;
$lastyear = 1993;

SELECT * FROM alumni
WHERE classyear BETWEEN $firstyear AND $lastyear

Fife_Club

Sorry, I spoke too soon about being able to do this myself.

Originally posted by eoghain
As for useing multiple years/states you will need to construct your query a bit differently. First build a comma separated list of the years/states that you want to use in the query (don't forget the quotes around each state i.e "NY", "CA"). Then put that variable into a query that looks like this:
SELECT *
FROM alumni
WHERE classyear IN ($list);
The IN keyword lets you pass in a comma separated list that will be used in the query like a list of OR values:
SELECT *
FROM alumni
WHERE classyear = 1994
OR classyear = 1995
OR classyear = 1996
I'm sure you can see how the first example is more readable and easier to used. [/B]

Sorry, I'm not sure how to properly construct the $list part. I replaced classyear = $classyear; with classyear IN ($list); but I don't know what to do now to make $list refer to anything.

Just incase it matters, I'll explain that I actually need to have the query originate from the address bar because I will have dozens of posssible value comboss (rather than have a page for every possible combo of values). In this example, I will have one hypertext link for each individual year from 1980 to present, PLUS a link for each decade (this is where the multiples comes in). For my locations page I will have a link to each individual state PLUS a link for ie. New England (where the link will be constructed to include all of the individual New England states) and so forth.

The paragraph above may not make a difference but o my main hang up is how do I actually make the $list part? What am I missing? Does a hypoertext link point to "?list=list1" and then in my page I have what list1 equals or does it work some other way?
😕

P.S. Your suggestion about using the single quotes for non-integers worked like a charm. Thanks!

eoghain

Ok lets assume that your address is this:

http://www.myclasspage.com/file.php?classyear=1990,1991,1992

In this instance just putting $classyear into the IN clause of your query will work just fine since it is formatted properly.

$query = "SELECT * FROM alumni WHERE classyear IN ($classyear)";
$result = mysql_query ($query);

-- In my first example $list was just a varible that represented the comma separated list of years, you could have just put $classyear in the query like I did above. And the beauty of using IN is that putting a single value between the quotes is valid syntax.

Now if you want to do the same thing with state you either need to make your link call your page like so:

http://www.myclasspage.com/file.php?state='NY','MA','PA'

Or you will have to parse what comes in the URL and build your string.

// Assuming that $state will hold NY,MA,PA no spaces no quotes
$state_array = [man]explode[/man](",",$state); // Make an array of states

$states = "";  // Blank states variable
$sep = ""; // setting up the separater character
// Loop through states array and build quote comma separated list
for (reset($state_array); $key = key($state_array); next($state_array) {
   $state .= $sep . "'" . $states[$key] . "'";
   $sep = ",";
}

$query = "SELECT * FROM alumni WHERE state IN ($states)";
$result = mysql_query ($query);

Now this code is more involved but it will basically take a list of comma separated values and build a single quote comma separated list of values. i.e NY,MA,PA to 'NY','MA','PA'

The explode function generates an array from a string and a delimiter. Then we walkthrough the array building our new string. The .= opperater is the same as saying $states = $states . "Something", the . being the concat opperator in PHP.

Fife_Club

Once again your advice works like a charm! But once again I ran into another problem.

There's nothing wrong with your code. Here it is in action: www.pstvalumni.com/directory/class.php?classyear=1984,1987

But my page used to include two other code snippets that seem to make everything malfunction if I leave them in there. First (and more simply) is if I leave "ORDER BY lastname" in the query. There are no variables to complicate things, and it seems to order things via lastname either way, but if I leave that in there it gives me "Sorry, no records were found!" (www.pstvalumni.com/directory/class2.php?classyear=1984,1987)

The other piece of code that causes everything to return no results is a little bit of code that limits the results to 20 per page and provides previous and next links. You can see it in action at www.pstvalumni.com/directory/index.php.

Here's the exact same code but with the "WHERE classyear IN" stuff added:

$query = "SELECT * FROM alumni WHERE classyear IN ($classyear) LIMIT $limit,$per_page";
$result = mysql_query ($query);

if(!isset($page)) { $page = 0; }
$per_page = 20;
$limit = $page * $per_page;

And here you can see it in action (not working correctly): www.pstvalumni.com/directory/class3.php?classyear=2000,2001.

I'm not too worried about the "order" issue because it seems to end up in the right order by accident anyway, but is it not possible to do the "where multiple classyear" thing AND the "limit to 20 per page" thing at the same time? That is my final goal anyway. (I want to keep the limit function incases where the WHERE statement gives back lots of results)

Thanks AGAIN. You've been a HUGE help!!!

eoghain

Your ORDER BY clause shouldn't have any effect on your data set. You should check your syntax to make sure everything is written correctly:

SELECT *
FROM alumni
WHERE classyear IN (1998, 2000)
ORDER BY lastname;

As long as lastname is a valid column in the alumni table this query is perfectly good and should return your data set ordered appropriately.

Now as for the LIMIT statement, I don't use MySQL so I don't have much exposure to this. But if your query works using the = statement then it should work just as good with the IN statement. I'd suggest you double check your syntax on everything.

If you can't find the bug try posting larger sections of your code and I'll give it a look.

Fife_Club

Yup. I had the order before the where. And in the other case I (stupidly) forgot to define the variables before using them in the querry. I've got to be more careful on the order of my code.

THANKS SO MUCH, EVERYBODY!!!

Here's the results. A page allowing filter of multiple years and limiting results to 20 per page.
http://www.pstvalumni.com/directory/class3.php?classyear=1993,1994,1995
(I'll leave this test page there a few days incase anyone wants to see the results)

😃 😃 😃 😃

Fife_Club

I am so sorry. Everytime I post that everything is fine I speak too soon. I ran into trouble again.

Here's a link showing how the classyear example works perfectly with one value (www.pstvalumni.com/directory/class.php?classyear=1994) and here's a link showing how it works perfectly with multiple values (www.pstvalumni.com/directory/class.php?classyear=1992,1993,1994).

So now I moved on to a non-integer example, such as "state". First I altered the code just a little and put 'single quotes' around '$state'. Here's the altered code:

$query = "SELECT * FROM alumni WHERE state IN ('$state') LIMIT $limit,$per_page";
$result = mysql_query ($query);

If you have only one state this code works fine (check either www.pstvalumni.com/directory/location.php?state=NC or www.pstvalumni.com/directory/location.php?state=GA), but when I try to add multiple states in the same querry string it doesn't work anymore (www.pstvalumni.com/directory/location.php?state=NC,GA). So next I tried your second suggestion with the explode stuff and it crashes on me (www.pstvalumni.com/directory/location2.php?state=NC).

So what have I done wrong? I'm sooooooo close. (I've even tried the suggestion of putting states in single quotes ('NC','GA') but that doesn't work with either one state or multiple states.)
😕

(If you need to see the entire code just ask. I just thought I should avoid all the clutter.)

Thanks so much! 😃

eoghain

Well the error you are receiving should tell you everything that you need to know:

Parse error: parse error, unexpected '[' in /home/pstvalu/public_html/directory/location2.php on line 12

Go to line 12 and see what is wrong. More than likely you used a [ instead of a ( or you just typed in to many [. If you can't see the problem with it then post that line and the lines around it and I'll take a look at it.

Fife_Club

<?php



$state_array = [man]explode[/man](",",$state); 



$states = "";

$sep = ""; 



for (reset($state_array); $key = key($state_array); next($state_array) {

   $state .= $sep . "'" . $states[$key] . "'";

   $sep = ",";

}



$db = mysql_connect("localhost", "xxxxxxxxx", "xxxxxxxxxx");

mysql_select_db("pstvalu_directory",$db);



if(!isset($page)) { $page = 0; }

$per_page = 20;

$limit = $page * $per_page;



$query = "SELECT * FROM alumni WHERE state IN ($states) LIMIT $limit,$per_page";

$result = mysql_query ($query);



if (@mysql_num_rows($result)) 

{ 

print "<table border=1>\n"; 

print "<tr> 



<td><b><u>Name</u></b></td>

<td><b><u>Class</u></b></td> 

<td><b><u>Location</u></b> 



</tr>\n"; 

while($row = mysql_fetch_array($result)) { 

print "<tr>\n"; 



print "<td><a href=\"info.php?id=".$row['id']."\">".$row['lastname']; 

if (!empty($row['maidenname'])) print " (".$row['maidenname'].")\n"; 

print ",&nbsp;".$row['firstname']."</a></td>\n"; 



print "<td>".$row['classyear']."</td>\n"; 



print "<td>".$row['city']; 

if (!empty($row['state'])) print ",&nbsp;\n"; 

print "".$row['state']."</td>\n"; 



print "</tr>\n"; 



} 



print "</table>\n"; 



} 

else { 

echo "Sorry, no records were found!"; 

} 



echo "<p>";

if ($page['0']) print "<b><a href='$PHP_SELF?state=".($state)."&page=".($page-1)."'>Previous</a></b>\n"; 

echo "&nbsp; &nbsp;";

if (@mysql_num_rows($result)) print "<a href='$PHP_SELF?state=".($state)."&page=".($page+1)."'>Next</a></b>\n"; 



?>

The 12th line would be $state_array = [man]explode/man;

P.S. I tried the "simpler"method you suggested with the single quotes but that didn't return any results (http://www.pstvalumni.com/directory/location3.php?state='NC')

eoghain

Ok your problem is that you copied my code exactly and didn't take the time to figure out what it was doing.

The message board that we are using has the ability to add tags, one of the tags that they added was:

[man][/man]

This tag turns what is in between the tags into a link to www.php.net so that when someone uses a tag they can give an easy link for the reader to follow to learn about the function.

So to make your code work change line 12 to look like this:

$state_array = explode(",",$state);

Fife_Club

I made that adjustment and now I get a parse error on line 16. Line 16 is this one:

Parse error: parse error, unexpected '{', expecting ')' in /home/pstvalu/public_html/directory/location2.php on line 16

for (reset($state_array); $key = key($state_array); next($state_array) {

Bunkermaster

Originally posted by Fife Club
for (reset($state_array); $key = key($state_array); next($state_array) {
[/B]

close parenthesis

eoghain

Bunkermaster is correct. You need to close the for loop.

Are you trying to learn how to program in PHP or are you just coping code and pasting it in?

I can't guarantee the integrity of my code cause I'm just typing in examples I'm not testing them out and making sure they run. So take some time to solve your own problems, this is such a simple error that you should be able to spot it without anyones help.

Anyway here is the correct line:

for (reset($state_array); $key = key($state_array); next($state_array)) {

As you can see all that was needed was a ) after next($state_array).

I don't mind helping out, but I'm not going to write your code for you. If you aren't going to try to learn things what is the point in my helping you? I'm not getting paid to do your job.

eoghain

Fife_Club

Sorry about all that. I try to figure out problems first thru trial and error. The first thing I did was try adding a ")" to that line was but it didn't work (the parse code went away but I got no results). I went on to try other things like removing the "{" and moving it to different locations but that made things even worse. In short, I did try the most obvious stuff first before posting again.

Also, true, I'm not really trying to learn everything about php/mySQL coding but I'm not trying to have anybody else do all the coding for me either. I've taught myself the basics with tutorials but as you know, those don't always apply to particular real-world examples. In the beginning of this thread I ommitted all of the extra code that wasn't relevant to the situation, but as soon as we got into explode and key, it went beyond the low level basics that I understood and I was pretty much flying blind for those sections.

Oh well.

eoghain

That's fine, like I said I don't mind helping out, but I hate just being used for code. I know what it's like to be learning a new language, hell I'm doing it myself with java right now.

When you added the ) and got rid of the error, then you should have started looking at logic errors in the code since everything was now parsing correctly there were no syntax errors.

So to determine if there are logic errors you should add print statements before all of your database queries to output the query that you are running. This will at least let you know if you are running the queries that you expect to be running. I'd guess that something is going wrong when you are building the query, maybe the input to the for loop isn't in MA,NY,PA format and you are getting spaces and the like added when you aren't expecting them. Once you know what the queries are then you should be able to trace where the errors are being introduced.

Fife_Club

So you're saying to check each individual variable (via a simple print or echo's) to see if a) each variable is working in the first place, and b) to see if it may be working but not quite in the way I was expecting.

That's a good methodology. Maybe it's common practice in coding but since I'm new, I never thought of it before. I'll investigate it that way.