Page 1 of 1

Select statement issue

Posted: Fri Nov 25, 2005 10:54 pm
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;



}

Posted: Fri Nov 25, 2005 11:30 pm
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

Posted: Fri Nov 25, 2005 11:49 pm
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(

Posted: Sat Nov 26, 2005 1:11 am
by AGISB
I believe you used mysql reserved words to name your table rows.

Posted: Sat Nov 26, 2005 1:32 am
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

Posted: Sat Nov 26, 2005 3:49 am
by AGISB

Code: Select all

IF(`long`>$long,`long`,$long "
you lack a )

Posted: Sat Nov 26, 2005 7:58 am
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.

Posted: Sat Nov 26, 2005 8:01 am
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(

Posted: Sat Nov 26, 2005 8:05 am
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.

Posted: Sat Nov 26, 2005 8:51 am
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.