Not sure if this a PHP or MYSQL issue

[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

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;



}

[Rodney_H_]

I am definitely no expert at sql, but to me, it appears that the sql query statement is written by mixing syntaxes for INSERTING data INTO the db and SELECTING data for reading FROM the db.

For example:

<?php
// insertion query
$query = "INSERT INTO table_name
			(id, column1, column2, column3) 
            VALUES 
			(NULL, '$value1', '$value2', '$value3')";

// selection query
$query  = "SELECT id, column1, column2
			FROM table_name
			WHERE id=1";			
?>

Hope that makes sense...

So, your query, I believe needs to be re-written to something along the lines of:

$query = "INSERT INTO $weeklydb_table 
	   (`id`, `year`, `wk`, `cmp`, `att`, `yds`, `sack`, `td`, `int`, `long`)
	   VALUES
	   (NULL, '$year', '$wk', '$cmp', '$att', '$yds', '$sack', '$td', '$int', '$long')";  

[rbpd5015]

I believe you are right. The biggest issue I am having is MYSQL_PASSING has total stats for NFL players, whil MYSQL_GBGPASSING has the stats broken down into weeks. So I need to take the stats from MYSQL_PASSING and - them from MYSQL_GBGPASSING to get the correct stats, which I try to due here.

."SELECT id, `year`, $wk, $cmp-cmp, $att-att, $yds-yds, $sack-sack, $td-td, $int-`int`, IF(`long`>$long,`long`,$long "

[Weedpacket]

IF(long>65,long,65

Isn't there something missing here?

[rbpd5015]

yeah sorry fixed that.

Still gives me same error, makes believe it is not getting that far in the code.

MAtt

[jeepin81]

I am not 100% about this but shouldn't you lose the comma before the "IF"? If this is not right, I apologize before hand.

Original Code:
t-att, $yds-yds, $sack-sack, $td-td, $int-int, IF(long>$long,long,$long "

Shouldn't id be:
t-att, $yds-yds, $sack-sack, $td-td, $int-int IF(long>$long,long,$long "

Again, I apologize if this is not right but that is what i would try if I had an SQL syntax error.

Best-

[rbpd5015]

Sorry that seemed not to work,
Thanks anyways JEep

MAtt

[Rodney_H_]

rbpd5015 wrote:

The biggest issue I am having is MYSQL_PASSING has total stats for NFL players, whil MYSQL_GBGPASSING has the stats broken down into weeks. So I need to take the stats from MYSQL_PASSING and - them from MYSQL_GBGPASSING to get the correct stats, which I try to due here.

If that is the case, then wouldn't you need to do two queries? One to get the base values, do your mathematics, and then insert the new values into the table via a second query?

I am confused, clearly. However, if you can do this in one query, don't strings need to be inside single quotes, so $att-att might possibly need to be '$att-att' for example. But, like I said, I am grasping at straws here.... lol

[rbpd5015]

Rodney,

Yeah I ran a query to start let me back the code up and show you

function sql_insertPlayerPassingStats($id, $year, $table)
{
	$db_table = MYSQL_PASSING;

if (!sql_emptyStats($db_table, $id, $year))
	return errorPrint("The call to sql_emptyStats() in sql_insertPlayerPassingStats($id, $year, $table) failed");

$cmp = $table[2];	$att = $table[3];	$yds = $table[4];	$sack = $table[7];	$td = $table[8];
$int = $table[9];	$long = $table[10];	

$query = "INSERT INTO $db_table "
		."(`id`, `year`, cmp, att, yds, sack, td, `int`, `long`) "
		."VALUES ($id, $year, $cmp, $att, $yds, $sack, $td, $int, $long)";
$result = @mysql_query($query);
if ($result == false)
	return errorPrint("The following query in sql_insertPlayerSeasonPassingStats($id, $year, $table); failed: <p><tt>$query</tt></p>");

return true;
}
	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;

}

See the 1st function sql_insertPlayerPassingStats takes the data and puts it into MYSQL_PASSING

the 2nd function then takes then puts the correct data in the weekly stats.

This seems to be my issue Rodney
Testing it more the problem has to be here

."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

."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

[Rodney_H_]

Matt:

I am definitely a little slow on the uptake here, lol, so you will have to bear with my questions...

I think I understand why you are doing this: "$cmp-cmp". However, isn't the syntax "$cmp - cmp" utilizing a variable, "$cmp", and subtracting the value of a constant, "cmp", from it?

Is there a way you can establish this weeks' stats and insert them directly without needing math at all? In other words, if you already know that: $cmp = 12 && $att = 24 for week 1 for player 333, can't you just insert those values?

If you can't, can you simplify things by doing the maths prior to insertion and writting your sql query statement?

[Weedpacket]

Might I additionally suggest that perhaps some of the problem comes from poorly-named columns? It's not just that they're terse - that's not so bad (but when you come back to it in six months' and nine projects' time, will you know what's stored in the "int" field?) - but the big problem is that so many of them conflict with reserved words (or might conflict with reserved words) that you have to quote (or might have to quote) them all over the place and you're being inconsistent about that. In short: using ` to quote a column name because it might be reserved is a last resort - not something to be made into a habit.