Page 1 of 1

Is this PHP or MySQL Retching on my Keyboard?

Posted: Sat Mar 24, 2007 7:46 pm
by dougp25
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I have this rather lengthy string in my PHP code.  When I copy it out of the editor, and paste it into phpMyAdmin, it executes perfectly.  On a web page, it constantly retches up bile with this little cutie:

You have an error in your SQL syntax, check the manual for proper syntax near
'.teacher_schedule_termid = grade_terms.grade_terms_id) INNER JOIN grade_subjects' at line 1

And here's the string:

Code: Select all

$sSQL="SELECT teacher_schedule_id, grade_terms.grade_terms_desc,
grade_subjects.grade_subject_desc, teacher_schedule_classperiod,
teacher_schedule_id FROM ((teacher_schedule INNER JOIN grade_terms ON
teacher_schedule_termid =
grade_terms_id) INNER JOIN grade_subjects ON teacher_schedule_subjectid =
grade_subject_id) INNER JOIN school_years ON teacher_schedule_year =
school_years.school_years_id
WHERE teacher_schedule_id = ANY(SELECT student_schedule_schedid FROM
student_schedule WHERE student_schedule_studentid =$studentid) ORDER BY
grade_terms.grade_terms_desc,
teacher_schedule.teacher_schedule_classperiod";
I would lay the blame at the feet of MySQL, but like I said if I copy in this query to phpMyAdmin (and substitute a studentid in place of $studentid) it executes perfectly.

Appreciate any help.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Sat Mar 24, 2007 10:08 pm
by volka
You wrote the whole query in one line? Why?
Is the script running with error_reporting=E_ALL (and display_errors=on) ?
Did you print $sSQL before sending it to mysql?

Posted: Sun Mar 25, 2007 10:46 am
by dougp25
I am not sure why I wrote it all in one line, except to say I always do. That's just the way I've always done it.
No, I am not running with error reporting, excellent Idea, I will do that.
Yup, if I 'echo $sSQL' to the browser, it looks fine. I can then copy it from the browser window, paste it into phpMyAdmin, and again, it executes perfectly.

I'm beginning to think it's a 3rd party issue, I use something from JVMultimedia called ez_results and ez_sql. It's pretty smooth about querying sql and displaying nice html. So that may be where my errror lays.

Thanks for the help. If anyone thinks of anything else, please shout at me!

Posted: Sun Mar 25, 2007 10:57 am
by volka
Is it the same mysql server you're sending the queries to with your php script and phpmyadmin?

What does

Code: Select all

echo '<div>Debug, client-info: ', mysql_get_client_info(), "</div>\n";
echo '<div>Debug, host-info: ', mysql_get_host_info(), "</div>\n";
echo '<div>Debug, server-info: ', mysql_get_server_info(), "</div>\n";
inserted after the failing query print?

Posted: Sun Mar 25, 2007 11:37 am
by dougp25
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Client info 5.0.27
Host-info Localhost via UNIX socket
server-info 5.0.27

Basically, ez_sql just remaps commands kind of.
So instead of saying mysql_query, we just say $db->query (of course it does a lot more, this is just the most basic example).

This is DEFINITELY something with the 3rd party stuff.
If I use the ez_sql class, and say $db->get_results($sSQL), it's fine. no errors.
It's only when i ask the ez_results class to query the db and html format them, that I am getting the error.

This is the function from ez_results where we query the DB:

Code: Select all

*	$ez_results->query_mysql
		*
		*	Perform results query (mysql & ezSQL) can use normal queries
		*
		*	$query = 'SELECT user, name, password FROM users'
		*/

		function query_mysql($query)
		{
			global ${$this->ez_sql_object};
			
			// Make sure query is not on multiple lines
			$query = str_replace("\n", '', $query);
			
			// make sure that start row is set to zero if first call
			$this->init_start_row();
			
			// get total number of results
			$this->get_num_results($query);

			// Do query
			$this->results = $db->get_results($query . " LIMIT {$_REQUEST['BRSR']},$this->num_results_per_page",ARRAY_N);

			$this->cur_num_results = count($this->results);
-----

Not that prolly helps! But I think I am close.

Thanks for the help! Shout back if you see something, functions have never been my strongpoint


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Sun Mar 25, 2007 11:45 am
by dougp25
I figured it out.

Ez_SQL hates it when the query spreads to a second line, and it had. So I went into pico and removed all the spaces, and make it one GIGANTIC line of code. Bingo, it works.

Thanks volka for the suggestions!