Query headache!

jasonmartin

Below is a query that I have that I can't figure what's wrong. It is very strange.

The code directly below works fine by itself & inserts a row into the rounds table.

$total = $h1+$h2+$h3+$h4+$h5+$h6+$h7+$h8+$h9;

$result=MYSQL_QUERY("INSERT INTO rounds (name,date,course,course_id,tee,type,h1,h2,h3,h4,h5,h6,h7,h8,h9,total,total_f9) "."VALUES  ('$name','$date','$course_name','$course_id','$tees','$hole_type','$h1','$h2','$h3','$h4','$h5','$h6','$h7','$h8','$h9','$total','$total_f9')");

echo "We have entered your scores for $course_name. Your total score was $total<P>";

But, when I add this code below it acts very strange. Sometimes it will insert the row from the query above or it will insert the data from the insert below. Basically the code below is querying the row just inserted above & then selects data from it & another table & inserts several rows in another table.

The weirdest part is the case when the query below inserts rows into its table, but the query above doesn't insert its row & the query below gathers its data from the just inserted row from the query above, yet it never gets inserted into the table.

$query = "SELECT rounds.h1,rounds.h2,rounds.h3,rounds.h4,rounds.h5,rounds.h6,rounds.h7,rounds.h8,rounds.h9,courses.h1_par,courses.h2_par,courses.h3_par,courses.h4_par,courses.h5_par,courses.h6_par,courses.h7_par,courses.h8_par,courses.h9_par FROM rounds,courses where rounds.name='$name' and rounds.date='$date' and rounds.course_id=$course_id and courses.course_id=$course_id";

$query_result_handle = mysql_query ($query);

for ($count = 1; $row = mysql_fetch_row ($query_result_handle); ++$count)
{

// The StatProcessor_f9 function works fine & all of it's variable are correct.

$score_type1 = StatProcessor_f9($row[0],h1_par,$course_id);
$score_type2 = StatProcessor_f9($row[1],h2_par,$course_id);
$score_type3 = StatProcessor_f9($row[2],h3_par,$course_id);
$score_type4 = StatProcessor_f9($row[3],h4_par,$course_id);
$score_type5 = StatProcessor_f9($row[4],h5_par,$course_id);
$score_type6 = StatProcessor_f9($row[5],h6_par,$course_id);
$score_type7 = StatProcessor_f9($row[6],h7_par,$course_id);
$score_type8 = StatProcessor_f9($row[7],h8_par,$course_id);
$score_type9 = StatProcessor_f9($row[8],h9_par,$course_id);


$result2=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','1','$row[9]','$row[0]','$score_type1')");
$result3=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','2','$row[10]','$row[1]','$score_type2')");
$result4=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','3','$row[11]','$row[2]','$score_type3')");
$result5=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','4','$row[12]','$row[3]','$score_type4')");
$result6=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','5','$row[13]','$row[4]','$score_type5')");
$result7=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','6','$row[14]','$row[5]','$score_type6')");
$result8=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','7','$row[15]','$row[6]','$score_type7')");
$result9=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','8','$row[16]','$row[7]','$score_type8')");
$result10=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','9','$row[17]','$row[8]','$score_type9')");

	}

Any help is greatly appreciated.

Thanks,

Jason Martin

thepriest

I've had trouble with long queries, inserts and stuff.
This may be your trouble aswell, but I don't know.

Does mysql support aliases (I assume so as they are fairly fundamental)

So your query with aliases is:
query = "SELECT r.h1,r.h2,r.h3,r.h4,r.h5,r.h6,r.h7,r.h8,r.h9,c.h1_par,c.h2_par,c.h3_par,c.h4_par,c.h5_par,c.h6_par,c.h7_par,c.h8_par,c.h9_par FROM rounds r,courses c where r.name='$name' and r.date='$date' and r.course_id=$course_id and c.course_id=$course_id";

Thats all I can do here.
hope it helps, stef

jasonmartin

I tried what you suggested (which does work in mysql), but no difference. I put some echo statements in there for several of the variables & they are there, so the query is be working. It has to be the insert statements that are causing the problems.

I placed error checking on the first insert, but it returns 0 for no error.

What really baffles me is that on some occasions it will insert the original row into the rounds table, but won't insert any rows into the stats table. On other occasions it will enter rows into the stats table & not insert the row into the rounds table. And then on some occasions it won't insert rows in either table. And on each occasion I am entering the same information to be inserted.

$query = "SELECT r.h1,r.h2,r.h3,r.h4,r.h5,r.h6,r.h7,r.h8,r.h9,c.h1_par,c.h2_par,c.h3_par,c.h4_par,c.h5_par,c.h6_par,c.h7_par,c.h8_par,c.h9_par FROM rounds r,courses c where r.name='$name' and r.date='$date' and r.course_id=$course_id and c.course_id=$course_id";

echo mysql_errno().": ".mysql_error()."<BR>";

$query_result_handle = mysql_query ($query);

for ($count = 1; $row = mysql_fetch_row ($query_result_handle); ++$count)
{

echo "h1 = $row[0]<br>";
echo "h2 = $row[1]<br>";
echo "h3 = $row[2]<br>";
echo "h4 = $row[3]<br>";
echo "h5 = $row[4]<br>";
echo "h6 = $row[5]<br>";
echo "h7 = $row[6]<br>";
echo "h8 = $row[7]<br>";
echo "h9 = $row[8]<br>";

$result2=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','1','$row[9]','$row[0]','$score_type1')");
echo mysql_errno().": ".mysql_error()."<BR>";
$result3=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','2','$row[10]','$row[1]','$score_type2')");
$result4=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','3','$row[11]','$row[2]','$score_type3')");
$result5=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','4','$row[12]','$row[3]','$score_type4')");
$result6=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','5','$row[13]','$row[4]','$score_type5')");
$result7=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','6','$row[14]','$row[5]','$score_type6')");
$result8=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','7','$row[15]','$row[6]','$score_type7')");
$result9=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','8','$row[16]','$row[7]','$score_type8')");
$result10=MYSQL_QUERY("INSERT INTO stats "."VALUES ('','$name','$course_name',$course_id,'$date','9','$row[17]','$row[8]','$score_type9')");

Thanks,

Jason Martin