Hello. It’s been years since I’ve been here and received lots of wonderful help back then. I have just now decided to code AS A HOBBY Php again and found out I needed to change from mysql to mysqli. Most of that isn’t a problem, but I ran into an issue with this snippet. It reads from that database into an array ( I hope ) called $info, but I am having trouble with the FOR command. I can’t make it sort through all the files found and print them out. Any help would be appreciated. I get an error with this part: $member = mysqli_fetch_array($info);
$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);
$team_id = 1;
$sql = "SELECT * FROM schools WHERE id = '$team_id'";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0 ) {
$teaminfo = mysqli_fetch_assoc($result);
$team = $teaminfo['school'];
$teamstate = $teaminfo['state_id'];
$teammascot = $teaminfo['mascot_id'];
$sql = "SELECT * FROM states WHERE id = '$teamstate'";
$result = mysqli_query($conn, $sql);
$stateinfo = mysqli_fetch_assoc($result);
$state = $stateinfo['state'];
$sql = "SELECT * FROM mascots WHERE id = '$teammascot'";
$result = mysqli_query($conn, $sql);
$mascotinfo = mysqli_fetch_assoc($result);
$mascot = $mascotinfo['mascot'];
ECHO 'Schedule Listing for: ' . $team . ' ' . $mascot . ', ' . $state;
} else {
echo 'No team was found!';
exit();
}
echo '<TABLE WIDTH=960 BORDER=0 CELLSPACING=1>
<TR bgcolor=#ccc>
<TH>ID</TH><TH>DATE</TH><TH>WEEK</TH><TH>-L-</TH><TH>OPPONENT</TH><TH>-F-</TH><TH>-A-</TH><TH>-C-</TH><TH>-P-</TH><TH>---</TH><TH>ADMIN OPTIONS</TH>
</TR>';
$sql = "SELECT * FROM games WHERE ( home_id = '$team_id' OR guest_id = '$team_id' ) ORDER BY week";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0 ) {
$info = mysqli_fetch_array($result);
} else {
echo '</TABLE>No games found in database!';
exit();
}
$numofrows = mysqli_num_rows($result);
echo $numofrows;
for($i = 0; $i < $numofrows; $i++) {
$member = mysqli_fetch_array($info);
if($i % 2) { //this means if there is a remainder
echo "<TR bgcolor=\"#EEEEEE\">";
} else { //if there isn't a remainder we will do the else
echo "<TR bgcolor=\"#FFFFFF\">";
}
$id = $member['id'];
$info is supposed to gather all the info using SELECT, but I can’t figure out with the FOR how to print out what is loaded into it. Sorry if I am not making much sense.
You might consider doing ONE query instead of FOUR and building a result data array $games you can then loop through with FOR. This sample is COMPLETELY UNTESTED.
<?php
//$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);
$conn = new mysqli($dbServername, $dbUsername, $dbPassword, $dbName);
$team_id = 1;
$games = array();
//DEFINE ALL FIELDS NEEDED FROM games TABLE with table prefix e.g. ga.id
$sql_find_team = "SELECT
sc.school
, st.state
, ma.mascot
, ga.id
FROM schools AS sc
LEFT JOIN states AS st
ON st.id = sc.state_id
LEFT JOIN mascots AS ma
ON ma.id = sc.mascot_id
LEFT JOIN games AS ga
ON ga.home_id = sc.id OR ga.guest_id = sc.id
WHERE sc.id = ?
ORDER BY ga.week";
$query_find_team = $conn->prepare($sql_find_team);
$query_find_team->bind_param("s", $team_id);
$query_find_team->execute();
$result_find_team = $query_find_team->get_result();
$numofrows = $result_find_team->num_rows;
if(!empty($numofrows)){
while($row = $result_find_team->fetch_assoc()){
$games[] = $row;
}
}else{
echo 'No team was found!';
}
//Table display
if(!empty($games)):
echo 'Schedule Listing for: ' . $games[0]['school'] . ' ' . $games[0]['mascot'] . ', ' . $games[0]['state'] . '<br />';
echo '<table width=960 border=0 cellspacing=1>
<tr style="background-color:#ccc">
<th>ID</th>
<th>DATE</th>
<th>WEEK</th>
<th>-L-</th>
<th>OPPONENT</th>
<th>-F-</th>
<th>-A-</th>
<th>-C-</th>
<th>-P-</th>
<th>---</th>
<th>ADMIN OPTIONS</th>
</tr>';
for($i=0; $i < $numofrows; $i++):
$bgcolor = (($i % 2) ? ' style="background-color:#EEEEEE;"' : ' style="background-color:#FFFFFF;"');
echo '<tr'.$bgcolor.'>
<td>'.$games[$i]['id'].'</td>
<td>DATE</td>
<td>WEEK</td>
<td>-L-</td>
<td>OPPONENT</td>
<td>-F-</td>
<td>-A-</td>
<td>-C-</td>
<td>-P-</td>
<td>---</td>
<td>ADMIN OPTIONS</td>
</tr>';
endfor;
echo '</table>';
endif;
?>
You don’t need that line. $info is ALREADY an array (if you look, it’s the same command as this one…). So instead of $member[‘id’], you’d use $info[‘id’]
I am really confused because this DID work before using mysql and not mysqli.
How do I, with my coding which is very subpar compared to the other shown, make it count through $info contents in the FOR loop?
$info = mysqli_fetch_array($result);
} else {
echo '</TABLE>No games found in database!';
exit();
}
$numofrows = mysqli_num_rows($result);
echo $numofrows;
for($i = 0; $i < $numofrows; $i++) {
if($i % 2) { //this means if there is a remainder
echo "<TR bgcolor=\"#EEEEEE\">";
} else { //if there isn't a remainder we will do the else
echo "<TR bgcolor=\"#FFFFFF\">";
}
$id = $info['id'];
If you are really set on returning a full result set and using FOR to loop through the rows then you want to use fetch_all.
minimally modified version.
<?php
$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);
$team_id = 1;
$sql = "SELECT * FROM schools WHERE id = '$team_id'";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0 ) {
$teaminfo = mysqli_fetch_assoc($result);
$team = $teaminfo['school'];
$teamstate = $teaminfo['state_id'];
$teammascot = $teaminfo['mascot_id'];
$sql = "SELECT * FROM states WHERE id = '$teamstate'";
$result = mysqli_query($conn, $sql);
$stateinfo = mysqli_fetch_assoc($result);
$state = $stateinfo['state'];
$sql = "SELECT * FROM mascots WHERE id = '$teammascot'";
$result = mysqli_query($conn, $sql);
$mascotinfo = mysqli_fetch_assoc($result);
$mascot = $mascotinfo['mascot'];
ECHO 'Schedule Listing for: ' . $team . ' ' . $mascot . ', ' . $state;
} else {
echo 'No team was found!';
exit();
}
echo '<TABLE WIDTH=960 BORDER=0 CELLSPACING=1>
<TR bgcolor=#ccc>
<TH>ID</TH><TH>DATE</TH><TH>WEEK</TH><TH>-L-</TH><TH>OPPONENT</TH><TH>-F-</TH><TH>-A-</TH><TH>-C-</TH><TH>-P-</TH><TH>---</TH><TH>ADMIN OPTIONS</TH>
</TR>';
$sql = "SELECT * FROM games WHERE ( home_id = '$team_id' OR guest_id = '$team_id' ) ORDER BY week";
$result = mysqli_query($conn, $sql);
$numofrows = mysqli_num_rows($result);
if (mysqli_num_rows($result) > 0 ) {
$info = mysqli_fetch_all($result,MYSQLI_ASSOC);
for($i = 0; $i < $numofrows; $i++) {
if($i % 2) { //this means if there is a remainder
echo '<tr bgcolor="#EEEEEE">
<td>'.$info[$i]['id'].'</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>';
} else { //if there isn't a remainder we will do the else
echo '<tr bgcolor="#FFFFFF">
<td>'.$info[$i]['id'].'</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>';
}
}
echo '</TABLE>';
} else {
echo '</TABLE>No games found in database!';
exit();
}
?>
Slightly off-topic, but if the main reason for your if…else around the remainder of the row count is to alter the background colour of the table row, have a look at this in your CSS:
That will set the background-colour for odd and even rows automatically, for all HTML tables. Of course you could target only specific tables by adding an id or class definition.
I wouldn’t be surprised if there’s a newer way to achieve that, I’m years behind the times in CSS styling and the like, and with what browsers support each “new” feature.
When there are results your current code fetches the first result as an array than passes that as an argument to the same function in the loop. The error is exactly what the error states. You’re passing the first result row into the function rather than the result resource.