Can't get recordset

[Chomauk]

I have the following code:

<?php
$sqlString = "CREATE TABLE WeeklyGames ";
$sqlString = $sqlString . "(VTeam varchar(13), VTeamNum tinyint(2),
HTeamNum Tinyint(2)); ";

$sqlString = $sqlString . " INSERT INTO WeeklyGames SELECT T.Team, G.VTeamNum, G.HTeamNum ";
$sqlString = $sqlString . " FROM Teams T INNER JOIN Games G ON T.TeamNum = G.VTeamNum ";
$sqlString = $sqlString . " WHERE G.WeekNum = 1; ";

$sqlString = $sqlString . " SELECT G.GameDay, WG.VTeam, G.VScore, T.Team, G.HScore ";
$sqlString = $sqlString . " FROM Games G, WeeklyGames WG ";
$sqlString = $sqlString . " INNER JOIN Teams T ON WG.HTeamNum = T.TeamNum ";
$sqlString = $sqlString . " WHERE G.WeekNum = 1 And WG.VTeamNum = G.VTeamNum ";
$sqlString = $sqlString . " ORDER BY G.GameDay, G.GameTime, WG.VTeam; ";

echo $sqlString;

include ("connect.php");
$result = mysql_query($sqlString, $server);
while ($ReturnRow= mysql_fetch_array($result)) {

echo $ReturnRow["Team"];
printf("<br />");
echo $ReturnRow["VTeam"];
printf("<br />");
echo $ReturnRow["HTeam"];
printf("<br />");    

}


$result = mysql_stmt_execute("Drop table WeeklyGames;", $server);

mysql_close($server);
?>

here is the actual code from the echo:
CREATE TABLE WeeklyGames (VTeam varchar(13), VTeamNum tinyint(2), HTeamNum Tinyint(2)); INSERT INTO WeeklyGames SELECT T.Team, G.VTeamNum, G.HTeamNum FROM Teams T INNER JOIN Games G ON T.TeamNum = G.VTeamNum WHERE G.WeekNum = 1; SELECT G.GameDay, WG.VTeam, G.VScore, T.Team, G.HScore FROM Games G, WeeklyGames WG INNER JOIN Teams T ON WG.HTeamNum = T.TeamNum WHERE G.WeekNum = 1 And WG.VTeamNum = G.VTeamNum ORDER BY G.GameDay, G.GameTime, WG.VTeam;
I get the following error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/virtual/site104/fst/var/

http://www/html/frawleyfootballpools/displayweeklygames.php

on line 23

Fatal error: Call to undefined function: mysql_stmt_execute() in /home/virtual/site104/fst/var/

http://www/html/frawleyfootballpools/displayweeklygames.php

on line 35


I tested the code in MySQL and it works so I'm assuming the error is with php or how I'm creating the temp table first or something. I've also tried mysql_stmt_execute &
mysql_fetch_row with similar errors in a mixture of creating the temp table first and executing that but nothing works. Any suggestions to save me would be greatly appreciated.


"Failure is the tuition you pay for success."
~ Walter Brunell ~

 

[LTeeple]

Trap your errors so you can see if its your connection script that fails.

$connection = mysql_connect("localhost:3306","username","password")
    or die("Couldn't make connection: " . mysql_error()); //on failiure, post error message
  $db = mysql_select_db("databasename", $connection)
    or die("Couldn't select database:" . mysql_error());

Cheers!
Laura

 

[TonyGroves]

I can't find any reference to mysql_stmt_execute() in the PHP API reference in P. DuBois's book "MySQL". I'm not a PHP user, but it would seem to me that mysql_query() should be used there.



-----
ALTER world DROP injustice, ADD peace;

 

[ericbrunson]

Can you run DDL in query? By definition it isn't one.

 

[Chomauk]

I don't know what a DDL is but I have found a solution... sort of. I split the query in two as follows:

<?php
$sqlString = "SELECT G.GameDay, G.GameTime, T.Team ";
$sqlString = $sqlString . " FROM Teams T ";
$sqlString = $sqlString . " INNER  JOIN Games G ON T.TeamNum = G.VTeamNum ";
$sqlString = $sqlString . " WHERE WeekNum = 1 ";
$sqlString = $sqlString . " Order By GameDay, GameTime ";

include ("connect.php");
$result1 = mysql_query($sqlString, $server);

$sqlString = "SELECT T.Team ";
$sqlString = $sqlString . " FROM Teams T ";
$sqlString = $sqlString . " INNER  JOIN Games G ON T.TeamNum = G.HTeamNum ";
$sqlString = $sqlString . " WHERE WeekNum = 1 ";
$sqlString = $sqlString . " Order By GameDay, GameTime ";
$result2 = mysql_query($sqlString, $server);

printf("<br />");
while ($ReturnRow1= mysql_fetch_array($result1) AND $ReturnRow2= mysql_fetch_array($result2)) {

  echo $ReturnRow1["Team"] . '  ' . $ReturnRow2["Team"];
  printf("<br />");
}

mysql_close($server);
?>
</form>

This works...but is there a way to combine the two queries? What I have is a lookup table and I want to return two different values from the lookup table for each row returned. Is there a way to do that?

thanks

"Failure is the tuition you pay for success."
~ Walter Brunell ~

 

[KarveR]

I'm guessing this should do you , the ORDER BY fields I hope are in Games (G) table?

$sqlString="SELECT G.GameDay, G.GameTime, T.Team 
FROM Teams T ,Games G 
WHERE T.TeamNum = G.VTeamNum 
AND WeekNum = 1 
Order By G.GameDay, G.GameTime "; // if these fields are in teams change to T

*possibly drop the order by to make sure you have all the data first, then if you have the right data apply ordering.

Also to reference your initial post, mysql_stmt_execute() is not a valid php function, therefore no records would have been returned, as no records were returned, mysql_fetch_array would not have have a valid link resource.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.