mysql_fetch_row issue

[angelclawdust]

unfortunately after making some good progress after my other error i seem to have hit another wall with this error :-

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /homepages/2/d398538194/htdocs/headtohead/instellingen.inc on line 704

ive looked at the code over and over again - ive searched google and various other posts with ppls issues but cant quite see where its going wrong.
Everything pointing to a tbl in the database is correct.
The connection is initiated at the top of the document in question also
im not sure how much code to add but just after the issue there is an ending ?> so ill paste up till i hit the opening tag

<?php



}



function get_value_average($player_id){

$sql = "SELECT u.player_value, t.team_stars ".

		"FROM 5v5_users AS u, 5v5_team AS t ".

		"WHERE u.user_id=$player_id ".

		"AND t.team_id=u.team_id ";

$result = mysql_query($sql);

$row = mysql_fetch_array($result);

//opgehaald speler value en team_stars [0] en [1]



$sql_rates = "SELECT rate FROM 5v5_player_rates WHERE player_id=$player_id ORDER BY match_id";

$result_rates = mysql_query($sql_rates);

//echo "<br>$sql_rates";

$total_rate = 0;

$total_matches = 0;

$player_value = $row[0];

$team_stars = $row[1];

while($row_rates = mysql_fetch_array($result_rates)){

	$total_matches++;

	$total_rate = $total_rate + $row_rates[0];

	$player_value = calculate_player_value($player_value, $team_stars, $row_rates[0]);

}



//echo "<br>aantalw: $total_matches, rate: $total_rate, player_value: $player_value";

if ($total_matches>0){

	$av_rate = round(($total_rate / $total_matches),2);

}else{

	$av_rate = "-";

}

//echo "<br>aantalw: $total_matches, rate: $total_rate, player_value: $player_value, av_rate: $av_rate";

$player_information[rate] = $av_rate;

$player_information[value] = $player_value;

$player_information[matches] = $total_matches;



//print_r($player_information);

return $player_information;

}



function calculate_player_value($player_value, $team_stars, $your_rating){

global $team_stars_max; //5

global $team_average; //7.5



/*

(your player value / $team_average) * your match rating

*/



//echo "<br>player_val = $player_value";

//echo "<br>team_av = $team_average";

//echo "<br>rating = $your_rating";

$sum1 = round(round(($player_value/$team_average)) * $your_rating);

//echo "<br>tot: $sum1";



/*

(sum1/100)*((team_stars_max-$team_stars)*10)

*/

$sum2 = ($sum1/100)*(($team_stars_max - $team_stars)*10);



$total_value = $sum1 + $sum2;

//echo "<br>total: $total_value";

return $total_value;

}

function get_match_info($game_id){
	$sql = "SELECT league_id, hometeam_id, awayteam_id, home_result, away_result FROM fixtures_result WHERE game_id=$game_id";
	//echo "<br>$sql";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	return $row;
}



function get_match_rating($match_id, $player_id){

$sql = "SELECT rate FROM 5v5_player_rates WHERE match_id=$match_id AND player_id=$player_id";

//echo "<br>$sql";

$result = mysql_query($sql);

$row = mysql_fetch_array($result);

return $row[0];

}



function insert_5v5_stats($game_id, $player_id, $level){

$sql = "INSERT INTO 5v5_player_rates (player_id, match_id, rate) ".

	"VALUES ($player_id, $game_id, '$level')";

//echo "<br>$sql";

mysql_query($sql) or die("An error occured, please try again");

}



function random_league(){

$sql = "SELECT league_name FROM league WHERE league_type!='cup' AND subscription='yes' ORDER BY RAND() limit 1";	

$result = mysql_query($sql);

$row = mysql_fetch_array($result);

return $row[0];

}



function random_team($league_id){

$sql = "SELECT t.team_name FROM team as t, competition as c where league_id='" . mysql_real_escape_string($league_id) . "' and c.team_id=t.team_id ORDER BY RAND() limit 1";	

$result = mysql_query($sql);

$row = mysql_fetch_array($result);

return $row[0];

}



function get_league_weeknr($league_id){

$sql = "SELECT league_weeknr FROM league WHERE league_id='" . mysql_real_escape_string($league_id) . "'";	



$result = mysql_query($sql);

$row = mysql_fetch_array($result);

return $row[0];

}





function show_not_played_fixtures($league="", $wide="small"){

if ($league=="")$league_id = get_league_id($league);



$weeknr = get_league_weeknr($league);

$sql = "SELECT league_id, hometeam_id, awayteam_id, game_id, week_nr ".

		"FROM fixtures_result ".

		"WHERE home_result IS NULL ";

		if ($league!="all"){

			$sql .= "AND league_id='" . mysql_real_escape_string($league) . "' AND week_nr < $weeknr ";

		}

		$sql .= "AND fixture_ignore='no' ";



		$sql .= "ORDER BY week_nr";

		//echo $sql;

$result = mysql_query($sql);



$colourclass = "fixturesLightRow";

while ($row = mysql_fetch_row($result)) {

	$hometeam = get_team_information($row[1]);

	$awayteam = get_team_information($row[2]);

	$league = urlencode(get_league_name($row[0]));

	$hometeamname = urlencode($hometeam[1]);

	$awayteamname = urlencode($awayteam[1]);



	$user_id = get_current_user_id($hometeam[0], $league_id);

	$user_name_home = get_user_name($user_id);



	$user_id = get_current_user_id($awayteam[0], $league_id);

	$user_name_away = get_user_name($user_id);



	if ($user_name_home != 'No User' AND $user_name_away != 'No User') {

	?>

The line its on about is as follows and can be found "28" lines up from the bottom of the above php

while ($row = mysql_fetch_row($result)) {

any suggestions or help would be great - sites looking good now and im hoping itll be done soon 😃
cheers in advance all 🆒

[Derokorian]

Have a read of this -> http://www.phpbuilder.com/board/showthread.php?p=10991229

You need to be checking if the query was successful or not before trying to fetch rows from it (because if it failed, there will be no rows to fetch!)

[angelclawdust]

yes i have read over that yes
i dont know if its cause ive been staring at it for too long or what but i just cant see where its not working 🙁
watch it be somin simple lol

[bradgrafelman]

angelclawdust;10994954 wrote:

yes i have read over that yes

Then why didn't you follow any of the debugging tips offered in that post?

angelclawdust;10994954 wrote:

i just cant see where its not working 🙁

And without any further info (such as what you'd get if you had followed the aforementioned debugging tips), neither can we.

[angelclawdust]

i did try
as mentioned elsewhere im not that gr8 with php tbh but trying is the key
ive never ever had to do a mysql_error() call so i have no idea where to put it ie in the same page as my above code - or in a plain page on the same server
i also dont know exactly what to put i the mysql_error() statement to ask for the info

could u eplain it or even how to configure the statement with the right info to be able to correct this error??

this site wasnt built by me - wasnt coded by me - and was messed up by the original coder when he threw his teddy out the pram a while ago... i just said id see what i could do but not promise anything
ive got most of it working - just get confused at times with how it works - google has helped alot tbf and so have you guys - i dont need an idiots guide lol just a better understanding of it to get the mysql_error() to give me the info you and i require to figure out where its gone wrong

[bradgrafelman]

Take a look at the manual page for [man]mysql_query/man. Code examples #1 and #2 both exemplify the basic tips I talked about in that post, namely checking to see if mysql_query() indicates an error has occurred (based on its return value) and, if so, outputting debug info such as the SQL query itself as well as the error message generated by MySQL via the use of [man]mysql_error/man.

[man]mysql_error/man itself is dead simple - it simply returns the error message as a string.

[angelclawdust]

kk so if i was to use this code

<?php
$link = mysql_connect("host", "username", "pass");

mysql_select_db("db", $link);
?>
<?php
// Formulate Query
// This is the best way to perform an SQL query
// For more examples, see mysql_real_escape_string()
$query = sprintf("SELECT league_id, hometeam_id, awayteam_id, game_id, week_nr FROM fixtures_result 
    WHERE home_result IS NULL",
    mysql_real_escape_string($league_id),
	mysql_real_escape_string($hometeam_id),
	mysql_real_escape_string($awayteam_id),
	mysql_real_escape_string($game_id),
    mysql_real_escape_string($week_nr));

// Perform Query
$result = mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

// Use result
// Attempting to print $result won't allow access to information in the resource
// One of the mysql result functions must be used
// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
while ($row = mysql_fetch_assoc($result)) {
    echo $row['league_id'];
    echo $row['hometeam_id'];
    echo $row['awayteam_id'];
    echo $row['game_id'];
	echo $row['week_nr'];
}

// Free the resources associated with the result set
// This is done automatically at the end of the script
mysql_free_result($result);
?>

host - username - pass - db edited 😉

would that get what your on about or would the above code be wrong?

[planetphp]

When debugging I usually just

echo mysql_error();

after any mysql_query() statement.

But your code looks like it should work.

[angelclawdust]

oki doki
the code above i had in a seperate page as i had no clue where to put it
thanks planetphp for telling me where to put it

it outputted the following

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 'AND fixture_ignore='no'ORDER BY week_nr' at line 1
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /homepages/2/d398538194/htdocs/headtohead/instellingen.inc on line 704

and this is where it is in the code

$weeknr = get_league_weeknr($league);

$sql = "SELECT league_id, hometeam_id, awayteam_id, game_id, week_nr ".

		"FROM fixtures_result ".

		"WHERE home_result IS NULL ";

		if ($league!="all"){

			$sql .= "AND league_id='" . mysql_real_escape_string($league) . "' AND week_nr < $weeknr ";

		}

		$sql .= "AND fixture_ignore='no' ";



		$sql .= "ORDER BY week_nr";

		//echo $sql;

$result = mysql_query($sql);

so what would that mean then as i dont see an issue with it if im honest lol

[tomhath]

Check what's in $weeknr, looks like things went south right after "' AND week_nr < $weeknr "

[angelclawdust]

when u say whats in it
do u mean the rows of tht table??
as where it say at top $week_nr and then $league would that mean from league table?
i ask as the call is mainly to the fixtures and results table which has a weeknr but the $league table has a league_weeknr

sry i am fairly new to it all but am plodding along 😃

[Roger_Ramjet]

Whenever you see that SQL syntax error message, whenever, immediately echo out the statement you have built. The message is telling you that your sql statement is miss-formed and so can not be executed. Echo it out and look at it, don't guess what it is but see what it is.

[johanafm]

It allready showed what the error is in this case.

AND fixture_ignore='no'ORDER BY week_nr

But if you have problems spotting where an error is, split your query over several lines.

Where this gives you "error on line 1"

SELECT league_id, hometeam_id, awayteam_id, game_id, week_nr FROM fixtures_result WHERE home_result IS NULL AND league_id='stuff' AND week_nr < 10 AND fixture_ignore='no'ORDER BY week_nr

This would give you "error on line 7"

"SELECT league_id, hometeam_id, awayteam_id,
game_id, week_nr
FROM fixtures_result
WHERE home_result IS NULL
AND league_id='stuff'
AND week_nr < 10
AND fixture_ignore='no'ORDER BY week_nr"