I have been converting an old script, and having a couple of problems in doing so -
Its a surname database, people search for a surname etc and brings a list of people for the searched name, the script is displaying the list correctly and showing peoples information when clicked correctly, the problem lies when I click on to next results (page numbers)
I click on another page of results and I get this sql error -
SQL Error Occurred : 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 '20' at line 1
I have viewed the coresponding page and cant see the problem - here is the page code;
[code]
Family History: Surname DATABASE - SUMMARY SEARCH RESULTS
DATABASE - SUMMARY SEARCH RESULTS
<?php
include("common/datacon.php");
$limitPerPage = 20; //Results per page
$cut_off = 10; //Number of pages displayed at once
if (!isset($_REQUEST[‘startLimit’])) $startLimit = 0; //Don’t change!
There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 206
STR: </
Error
SQL query:
$limitPerPage =20;
MySQL said: Documentation
#1064 - 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 '$limitPerPage = 20' at line 1
I’m talking about the SQL query that’s being run, i.e. “SELECT * FROM table WHERE where_clause”. I need to know EXACTLY what the query looks like in order to determine what the issue is.
EDIT:
I’m thinking you didn’t write this code yourself. Am I right?
We’re not here to meet deadlines or make a buck, we’re here to help fellow developers better understand the (quirks of the) PHP language.
I’ve asked for the entire SQL query, which you could have easily provided me by removing a simple set of comment slashes in your code. The code is basically already there. My signature gives a link to a debugging thread on this very forum which gives you plenty of tips and tricks, and should have at least given you the knowledge to give me the query I requested.
The reason Andy’s suggestion doesn’t solve your problem is because the if statements are syntactically correct, and construct a query that’s probably not the one messing up (this could have also been verified by removing said comment slashes and simply taking a peek at what exact query is being run, and where things go haywire). What I can see is that you’re taking $_GET data and inject it directly into your SQL query (which is unsafe by ANY definition of the word), and you get tangled up in the URL encoding of certain characters. However, that’s just guessing on my side, since I haven’t seen the query itself.
I have been very very polite and as I am needing a lil help (phphelp) I have come here,
I know you and others would not care if I met deadlines or not but the point is I am asking politely and not getting much back. If I knew exactly what to reply with etc I would of, a little bit of understanding and less arrogance would be very nice to see.
If perhaps you can post the results of the echo $cntquery; that you have (currently commented in the code above) so that we can see the actual query.
The Error message indicates it’s a problem with the query. As was pointed out it looks that it could be in the where clause as well since it’s created by a bunch of “IF” statements. It maybe something as simple as forgetting a space, but until we see the ACTUAL Query that causes the error, it’s difficult to say and even more difficult for us to chug through all the IF statements.
$cntquery = "select no_of_reco,surname,forenames,day1,year1,month1,event_type,event_plac,old_c_code from tyrrell_main where $where";
///////////////////////////////////////////////////////////
// BELOW IS THE CHANGE.
///////////////////////////////////////////////////////////
echo $cntquery . “
”; // Uncommented this line
die(); // Added this line to halt execution of the script
///////////////////////////////////////////////////////////
// ABOVE IS THE CHANGE.
///////////////////////////////////////////////////////////
Then Run the script that gets the error. You can then copy the actual outputted query that’s on the web page. It should be the last thing viewed on the page.
Paste that result in here for us to see.
It’s the actual query that gets sent to mysql that we need to see. It should look something like: select no_of_reco,surname,forenames,day1,year1,month1,event_type,event_plac … but there would/should be more including the WHERE clause (which is where I think the problem might be.
select no_of_reco,surname,forenames,day1,year1,month1,event_type,event_plac,old_c_code from tyrrell_main where lcase(surname_sp) like '' and lcase(forename_s) like '' and no_of_reco like ''and lcase(notes) like '' and lcase(old_c_code) like ''
then the code I got from the siteI ran it through mysql query window and got this result -
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0880 sec)
SQL query:
SELECT no_of_reco, surname, forenames, day1, year1, month1, event_type, event_plac, old_c_code
FROM tyrrell_main
WHERE lcase( surname_sp ) LIKE ''
AND lcase( forename_s ) LIKE ''
AND no_of_reco LIKE ''
AND lcase( notes ) LIKE ''
AND lcase( old_c_code ) LIKE ''
LIMIT 0 , 30
First of all why are you lcaseing all the field names. Especially since they are already lowercase.
Next, did you notice that your entire where clause shows that you want the records where surname_sp, forename_s, no_of_rec, notes, and old_c_code are all blank?
Is this what you intended?
Are surname_sp, forename_s, no_of_rec, notes, and old_c_code even fields in the database?
Perhaps you meant for it to be more like:
SELECT no_of_reco, surname, forenames, day1, year1, month1, event_type, event_plac, old_c_code
FROM tyrrell_main
WHERE surname LIKE lcase( surname_sp )
AND forename LIKE lcase( forename_s )
AND no_of_reco LIKE ''
AND notes LIKE lcase( notes )
AND old_c_code LIKE lcase( old_c_code )
LIMIT 0 , 30
Where the text that is RED is actually from variable data in your code.
There isn’t much for us to give back if you don’t give us the requested information. We’re not your personal debugger that you can demand to fix your code when something goes wrong there.
As a token of good will however, I took your code and slightly modified it to see what exactly it does. I took out the HTML and manually added the URL query string to run it in CLI, using error_reporting(E_ALL);. Here’s the output:
[code]Notice: Undefined variable: startLimit in C:Documents and Settings410670Applicationsphpruntest.php on line 12
Notice: Undefined variable: startLimit in C:Documents and Settings410670Applicationsphpruntest.php on line 13
Notice: Undefined variable: startLimit in C:Documents and Settings410670Applicationsphpruntest.php on line 14
select no_of_reco,surname,forenames,day1,year1,month1,event_type,event_plac,old_c_code from tyrrell_main where lcase(surname) like ‘smith’
select no_of_reco,surname,forenames,day1,year1,month1,event_type,event_plac,old_c_code from tyrrell_main where lcase(surname) like ‘smith’ order by old_c_code,event_plac, year1, month1, day1, no_of_reco limit ,20[/code]
That’s exactly what I needed to know, to tell you what the problem is (and, to be completely honest, it was your job to give me this, as it’s really not too hard to produce, took me about 5 minutes, including making it CLI-ready, an extra action you don’t even have to do).
First of all: get rid of the notices. Apparently you’re not initializing the variable $startLimit. Go do that. Just give it a default value, but don’t leave it (conditionally) empty. The first query being run is fine. Doesn’t pose a problem. The second query uses $startLimit, which is undefined. Hence the ‘LIMIT ,20’ part, which is what the script is tripping over.
This also shows that my guess about the URL encoding messing it up was wrong, and clearly shows why providing the requested information is such an important factor in solving your problem.
