Select statement issue

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rbpd5015
Forum Newbie
Posts: 8
Joined: Fri Nov 25, 2005 10:51 pm

Select statement issue

Post by rbpd5015 »

I have wrote a select statement and I am having problems using it.

The following query in sql_insertPlayerWeeklyPassingStats(21088, 2005, Array); failed:

INSERT INTO ind_gbgpassing (`id`, `year`, wk, cmp, att, yds, sack, td, `int`, `long`) SELECT id, `year`, 1, 21-cmp, 31-att, 311-yds, 1-sack, 3-td, 1-`int`, IF(`long`>65,`long`,65 FROM ind_passing WHERE id = 21088 AND `year` = 2005


MySQL said: 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 'FROM ind_passing WHERE id = 21088 AND `year` = 2005' at line 1



below is the code I use, do you guys understand why this is messing up. I am 99% sure it has to do with lines being produced like this
21-cmp, 31-att, 311-yds, 1-sack, 3-td, 1-`int`
Any help would sure be appreciated

Code: Select all

function sql_insertPlayerGbgPassingStats($id, $year, $table)
{
	$db_table = MYSQL_PASSING;
	$weeklydb_table = MYSQL_GBGPASSING;
	
	$cmp = $table[2]; $att = $table[3]; $yds = $table[4]; $sack = $table[7]; $td = $table[8];
	$int = $table[9]; $long = $table[10];  $wk = $_POST["wk"];
	
	$query = "INSERT INTO $weeklydb_table "
	   ."(`id`, `year`, wk, cmp, att, yds, sack, td, `int`, `long`) "
	   ."SELECT id, `year`, $wk, $cmp-cmp, $att-att, $yds-yds, $sack-sack, $td-td, $int-`int`, IF(`long`>$long,`long`,$long "
	   ."FROM $db_table "
	   ."WHERE id = $id AND `year` = $year ";  
	 $result = @mysql_query($query);
	 echo $query;
	 if ($result == false)
	  return errorPrint("The following query in sql_insertPlayerWeeklyPassingStats($id, $year, $table); failed: <p><tt>$query</tt></p>");
	
	 return true;



}
User avatar
trukfixer
Forum Contributor
Posts: 174
Joined: Fri May 21, 2004 3:14 pm
Location: Miami, Florida, USA

Post by trukfixer »

read your error messages - mysql is telling you what's wrong ..

note, Ive pasted a quote, highlighting the issue in red..
**********************************************************************************
INSERT INTO ind_gbgpassing (`id`, `year`, wk, cmp, att, yds, sack, td, `int`, `long`) SELECT id, `year`, 1, 21-cmp, 31-att, 311-yds, 1-sack, 3-td, 1-`int`, IF(`long`>65,`long`,65 FROM ind_passing WHERE id = 21088 AND `year` = 2005


MySQL said: 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 'FROM ind_passing WHERE id = 21088 AND `year` = 2005' at line 1
***************************************************************************************

Mysql is saying " Hey!! You forgot to close the parentheses!

IF(`long`>65,`long`,65) FROM
rbpd5015
Forum Newbie
Posts: 8
Joined: Fri Nov 25, 2005 10:51 pm

Post by rbpd5015 »

I fixed that and I get this error now


The following query in sql_insertPlayerWeeklyPassingStats(21088, 2005, Array); failed:

INSERT INTO ind_gbgpassing (`id`, `year`, wk, cmp, att, yds, sack, td, `int`, `long`) SELECT (id, `year`, 1, 21-cmp, 31-att, 311-yds, 1-sack, 3-td, 1-`int`, IF(`long`>65,`long`,65) FROM ind_passing WHERE id = 21088 AND `year` = 2005


MySQL said: 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 ' `year`, 1, 21-cmp, 31-att, 311-yds, 1-sack, 3-td, 1-`int`, IF(
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post by AGISB »

I believe you used mysql reserved words to name your table rows.
rbpd5015
Forum Newbie
Posts: 8
Joined: Fri Nov 25, 2005 10:51 pm

Post by rbpd5015 »

Testing it more the problem has to be here

Code: Select all

."SELECT id, `year`, $wk, $cmp-cmp, $att-att, $yds-yds, $sack-sack, $td-td, $int-`int`, IF(`long`>$long,`long`,$long "
cause I can change it to this and it works fine

Code: Select all

."SELECT id, `year`, $wk, $cmp, $att, $yds, $sack, $td, $int-, $Long "
The only problem is it imports the season stats into the week stats so It looks like this
lets say my week by week stats were this just cmp and att
12 24
13 20
21 30
it would look like this
ID YEAR WK CMP ATT blah blah
333 2005 1 12 24
333 2005 2 25 44
333 2005 3 46 74

thats why I tried to do this I tried to do this $cmp-cmp, $att-att.


Matt
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post by AGISB »

Code: Select all

IF(`long`>$long,`long`,$long "
you lack a )
User avatar
trukfixer
Forum Contributor
Posts: 174
Joined: Fri May 21, 2004 3:14 pm
Location: Miami, Florida, USA

Post by trukfixer »

Code: Select all

INSERT INTO ind_gbgpassing (id, year, wk, cmp, att, yds, sack, td, int, long) SELECT id, year, 1, 21-cmp, 31-att, 311-yds, 1-sack, 3-td, 1-int, IF(long>65,long,65) FROM ind_passing WHERE id = 21088 AND year = 2005;
try it without backticks , and make sure all parentheses match up - every opening ( must have a closing ) now see if the above query works, and we'll figure out reserved words one by one...

was I you, I'd figure out which are reserved words and change the field names such as year becomes stats_year int becomes stats_int, etc

I'd say RTFM at mysql, but mysql manual is rather clumsy to get through at times as far as getting syntax right.
rbpd5015
Forum Newbie
Posts: 8
Joined: Fri Nov 25, 2005 10:51 pm

Post by rbpd5015 »

I dont know why I keep pasting old code and error messages I cleaned that up along time ago it still does not function. Sorry If I sounded frustrated when I asked the question I knew about the ")". I thought I had cut the data for the question after I fixed it. So I apologize.

I am more concerned about this part guys "21-cmp, 31-att, 311-yds, 1-sack, 3-td, 1-`int`. Why is it doing this I think this is the issue.????

Matt


The following query in sql_insertPlayerWeeklyPassingStats(22845, 2005, Array); failed:

INSERT INTO ind_gbgpassing (`id`, `year`, wk, cmp, att, yds, sack, td, `int`, `long`) SELECT (id, `year`, 1, 21-cmp, 31-att, 311-yds, 1-sack, 3-td, 1-`int`, IF(`long`>65,`long`,65) FROM ind_passing WHERE id = 22845 AND year = 2005


MySQL said: 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 ' `year`, 1, 21-cmp, 31-att, 311-yds, 1-sack, 3-td, 1-`int`, IF(
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post by neophyte »

I don't know what's going on. I don't usually use backticks ` around field names. I might try eliminating them all from your query and see what happens.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What MySQL version are you using? Which PHP version are you using?

Another question, has the SELECT statement worked by itself outside of the INSERT statement you are trying to select into? In your DB administrator run just the SELECT query and see if that is throwing an error. If it doesn't, try manually running the query you posted here to see if it is giving you the same error.

Another thing you might want to do is write your query structured similar to your code to see if there are inconsistencies with parantheses:

Code: Select all

$sql = "INSERT INTO ind_gbgpassing (`id`, `year`, wk, cmp, att, yds, sack, td, `int`, `long`) 
            SELECT id, `year`, 1, 21-cmp, 31-att, 311-yds, 1-sack, 3-td, 1-`int`, IF(`long`>65,`long`,65) 
            FROM ind_passing 
            WHERE id = 22845 
            AND year = 2005";
As I cleaned this up I noticed that you had an opening parathesis after SELECT. You didn't need that. It was also never closed so that may be giving you some issues also.

Here is the MySQL 4.1 manual on INSERT... SELECT statements
Here is the MySQL 5.0 manual on INSERT... SELECT statements
Here is the MySQL 5.1 manual on INSERT... SELECT statements

Have a look at these for more information on INSERT... SELECT syntax and the what not.
Post Reply