Data not Inserted?

[NigeB]

I have written the query below to insert data into a table.

The query runs, I get no errors but no data is inserted.

Any suggestions as to my error(s) greatly appreciated

TIA

Nigel.

******************************************************

$result = mysql_query("SELECT DISTINCT Teams.Team FROM Teams"
while ($row=mysql_fetch_assoc($result)) {

$query = mysql_query("INSERT INTO seedtmp3(TRN,Team,Fastest_Time,Venue)
SELECT Ttimes.TRN, Teams.Team, Ttimes.Fastest_Time, Venues.Venue FROM Teams
LEFT JOIN Ttimes ON Teams.TRN=Ttimes.TRN
LEFT JOIN Venues ON Ttimes.Tournament_ID=Venues.Tournament_ID
WHERE Team=".$row['Team']."
ORDER BY Ttimes.Fastest_Time DESC
LIMIT 5"
}
mysql_free_result($result);

 

[sleipnir214]

You're not getting errors and no data is going into the database because you aren't actually performing the query.

I see one invocation of mysql_query(), and that one outside the while loop. It looks like you would want to perform mysql_query() inside the while loop, too.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[NigeB]

I have tried adding another mysql_query() inside the loop, but now get a parse error on the line indicated ****

Any pointers in the right direction appreciated.

TIA

Nigel.

$result = mysql_query("SELECT DISTINCT Teams.Team FROM Teams&quot
while ($row=mysql_fetch_assoc($result)) { ****

$query = mysql_query("INSERT INTO seedtmp3(TRN,Team,Fastest_Time,Venue)
SELECT Ttimes.TRN, Teams.Team, Ttimes.Fastest_Time, Venues.Venue FROM Teams
LEFT JOIN Ttimes ON Teams.TRN=Ttimes.TRN
LEFT JOIN Venues ON Ttimes.Tournament_ID=Venues.Tournament_ID
WHERE Team=".$row['Team']."
ORDER BY Ttimes.Fastest_Time DESC
LIMIT 5"
mysql_free_result($query); }

mysql_free_result($result);

 

[NigeB]

I have cleaned up the code some, but I am still having problems with the line -

WHERE Team = ".$myrow["Team"]."

I am getting syntax error code for this line onwards "Query1 failed : You have an error in your SQL syntax near 'Stars ORDER BY Ttimes.Fastest_Time DESC LIMIT 5'

Stars is the first team in the table.

TIA

Nigel.

**************************************************
Complete Code -

$query = "SELECT DISTINCT Team FROM Teams";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
while ($myrow=mysql_fetch_array($result)) {

$query1 = "INSERT INTO seedtmp3(TRN,Team,Fastest_Time,Date,Venue)
SELECT Ttimes.TRN, Teams.Team, Ttimes.Fastest_Time, Venues.Date, Venues.Venue FROM Teams
LEFT JOIN Ttimes ON Teams.TRN=Ttimes.TRN
LEFT JOIN Venues ON Ttimes.Tournament_ID=Venues.Tournament_ID
WHERE Team = ".$myrow["Team"]."
ORDER BY Venues.Date DESC
LIMIT 5";
$result1 = mysql_query($query1) or die("Query1 failed : " . mysql_error());
mysql_fetch_array($result1);
}

mysql_free_result($result);

 

[sleipnir214]

It looks like you're performing concatenation of three strings: the string "INSERT...WHERE Team = ", the string that is the value in $myrow, and the string "Team Order by...Limit 5".

If you're concatenating three strings, it should look something like:

$foo = "foo" . $bar . "foo";

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[towerbase]

I'm a newcomer to MySQL but don't you need the keyword VALUES after the table name in the INSERT clause ?

Andrew

 

[KamranFeroz]

Try this,

$result = mysql_query("SELECT DISTINCT t.Team Team FROM Teams t"

while ($row=mysql_fetch_array($result))
{
$team1 = $row['Team'];

$query = "INSERT INTO seedtmp3(TRN,Team,Fastest_Time,Venue)
SELECT tt.TRN, t.Team, tt.Fastest_Time, v.Venue FROM Teams t
LEFT JOIN Ttimes tt ON t.TRN=tt.TRN
LEFT JOIN Venues v ON tt.Tournament_ID=v.Tournament_ID
WHERE t.Team= '$team1'ORDER BY tt.Fastest_Time DESC
LIMIT 5"
}

Let me know if that works.

Best of luck.

 

[KamranFeroz]

Some code left, pls discard above code,

$result = mysql_query("SELECT DISTINCT t.Team Team FROM Teams t"

while ($row=mysql_fetch_array($result))
{
$team1 = $row['Team'];

$query = "INSERT INTO seedtmp3(TRN,Team,Fastest_Time,Venue)
SELECT tt.TRN, t.Team, tt.Fastest_Time, v.Venue FROM Teams t
LEFT JOIN Ttimes tt ON t.TRN=tt.TRN
LEFT JOIN Venues v ON tt.Tournament_ID=v.Tournament_ID
WHERE t.Team= '$team1'ORDER BY tt.Fastest_Time DESC
LIMIT 5"

$res = mysql_query($query);
}

Hopefully it will work.

 

[NigeB]

The final code (below) worked just fine.

Thanks for all the tips and instructions, sometimes at 3am you just miss the most obvious....

Thanks again guys.

Regards

Nigel.

********************************************

$query = "SELECT DISTINCT Team FROM Teams";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
while ($row=mysql_fetch_array($result)) {

$team1 = $row["Team"];
$query1 = "INSERT INTO seedtmp3(TRN,Team,Fastest_Time,Date,Venue)
SELECT Ttimes.TRN, Teams.Team, Ttimes.Fastest_Time, Venues.Date, Venues.Venue FROM Teams
LEFT JOIN Ttimes ON Teams.TRN=Ttimes.TRN
LEFT JOIN Venues ON Ttimes.Tournament_ID=Venues.Tournament_ID
WHERE Team = '$team1'
ORDER BY Venues.Date DESC
LIMIT 3";
$result1 = mysql_query($query1) or die("Query1 failed : " . mysql_error());
mysql_fetch_array($result1);
}

mysql_free_result($result);

 

[sleipnir214]

towerbase:
No, column names in an insert statement are optional.

You only need to use column names in an insert statement if you have to specify which insert value should be inserted into which column. You run into this when you are not inserting data into a column, such as an auto_increment or timestamp column, or when the order of the values does not match the order of the columns in the table.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!