I’m having some issues with a PHP script I’ve created for displaying users from a database table.
In simple terms, the page loads the images and some basic details of all users (from the $query call to the database), but for each user it
should also determine whether or not the user is “available” (a form on the previous page allows people to pick dates and “day or night”,
the idea being that while all users are shown, those who match that availability have a “I’m available” image display, whereas those whose
availability dfoesn’t match have the “I’m NOT available” image show. The $checkuser SQL query runs to determine which users are available.
My problem is that the “Available” image displays for users regardless of whether or not the user is available or not.
I echoed the $checkuser script and it’s carrying through the variables ok, and no error message occurs.
It looks as if the if ($availableuser == $username) bit isn’t working- or it’s assigning $availableuser regardless of whether or not it
matches the user.
Any ideas?
include ('inc/dbconnect.php');
if($_POST['checkavailability'])
{
$type=@$_POST['_Type'];
$date=@$_POST['_DateSelected'];
$daynight=@$_POST['_Time'];
// Build SQL Query
$query = "select RealName, Detail, ImageAvailable, ImageUnavailable from users"; // specify the table and field names for the SQL query
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// get results
$result = mysql_query($query) or die("Couldn't execute query");
echo '<table cellpadding="0" cellspacing="0" width="700" border="0"><tr>';
// display the results returned
while ($row= mysql_fetch_array($result)) {
$username= $row["UserName"];
$realname= $row["RealName"];
$detail= $row["Detail"];
$imageavailable= $row["ImageAvailable"];
$imageunavailable= $row["ImageUnavailable"];
$checkuser = "SELECT UserName FROM dates WHERE DateAvailable='$date' AND DayNight='$daynight'"; //Check to see if there are Locals
available
//echo $query;
//echo $checkuser;
$checkresult = mysql_query($checkuser) or die("Couldn't execute query");
while ($row= mysql_fetch_array($checkresult)) {
$availableuser= $row["UserName"];
$count++ ;
}
echo '<td align="left" width="200">' . $realname. '<br>' . $detail . '</td>';
if ($availableuser == $username)
{
echo '<td><img src="images/' . $imageavailable . '" width="100" height="150"></td>';
}
if (!$username == $availableuser)
{
echo '<td><img src="images/' . $imageunavailable . '" width="100" height="150"></td>';
}
if ( ($count % 3) == 0 ) {
echo '</tr><tr>';
}
$count++ ;
}
echo '</tr></table>';
}
Hi trufflepig,
In your first SQL statement you do not select a UserName field:
select RealName, Detail, ImageAvailable, ImageUnavailable from users
then later while looping through $result you try to set
$username= $row["UserName"];
. This will be NULL. Therefore your
$availableuser == $username
will never equal as your $availableuser will be a real user and your $username is NULL.
In this case var_dump() is your friend; if you were to wrap it around
var_dump($row["UserName"]);
you would see it is NULL, which would have tipped you in the right direction.
Hope this helps.
Steve
Thanks, that seems to have done the trick. Just need to format it nicely now. 
Thanks again
Great… Did you have a UserName field in the database or did you solve it another way?
Steve
Already had it in the db table so just needed adding to the query. The rest of the logic seems to work fine. Thanks
I have another issue with the script now- if a user is available on a particular day and time, and someone requests that day and time, then that user is displayed, with their “Available” image (which is great) but none of the other users (the ones who aren’t available) are displayed. I want all users to be shown, whether or not they’re available. The ones who are available, based on the criteria in the form, will have their “available” image show, and those who are not will have theior “unavailable” image show.
Although at the moment, if the user selects a date / time when no one is available, it DOES list all the users (although it only lists their images- it doesn’t list their details EXCEPT for the first user in the table, which is bizarre)
Any ideas how this can be solved?
Think I may have fixed that by using if ($availableuser !== $username) instead of if (!$availableuser == $username) - seems to work!
Yes that makes sense as the username when read as unavailable should not equal and available user. Glad you got this working!
Steve
Only problem now seems to be the arrangement of the rows. As you can see I set some code for it to add a new row every three records that are output- but for some reason the first row of records (images etc.) has two entries (the third table cell is empty) and then all the following rows are fine and have three each. I can’t see where it’s getting an instruction to only output two on the first table row!
Can you post your most up-to-date code please.
Steve
if($_POST['checkavailability'])
{
$type=@$_POST['_Type'];
$date=@$_POST['_DateSelected'];
$daynight=@$_POST['_Time'];
// Build SQL Query
$query = "select UserName, RealName, Detail, ImageAvailable, ImageUnavailable from users"; // specify the table and field names for the SQL query
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// get results
$result = mysql_query($query) or die("Couldn't execute query");
echo '<table cellpadding="10" cellspacing="10" width="700" border="0"><tr>';
// display the results returned
while ($row= mysql_fetch_array($result)) {
$username= $row["UserName"];
$realname= $row["RealName"];
$detail= $row["Detail"];
$imageavailable= $row["ImageAvailable"];
$imageunavailable= $row["ImageUnavailable"];
$checkuser = "SELECT UserName FROM dates WHERE DateAvailable='$date' AND DayNight='$daynight'"; //Check to see if there are Users available
//echo $query;
//echo $checkuser;
$checkresult = mysql_query($checkuser) or die("Couldn't execute query");
while ($row= mysql_fetch_array($checkresult)) {
$availableuser= $row["UserName"];
$count++ ;
}
if ($availableuser == $username)
{
echo '<td><img src="images/' . $imageavailable . '" width="150" height="150"><br /><strong>' . $realname . '</strong><br />' . $detail. '</td>';
}
if ($availableuser !== $username)
{
echo '<td><img src="images/' . $imageunavailable . '" width="150" height="150"><br /><strong>' . $realname. '</strong><br />' . $detail. '</td>';
}
if ( ($count % 3) == 0 ) {
echo '</tr><tr>';
}
$count++ ;
}
echo '</tr></table>';
}
@trufflepig
Hi can you output var_dump($checkresult)? Have you checked that the first row in the array has a value?
Steve
Well, the output I get from that is this: resource(138) of type (mysql result)
And that’s all I get…???
1st row definitely has a value as it’s my first user account I set up (a test user). It’s weird, all the records are being outputted, but the first one is now appearing on a line by itself- so the first row has just that one record, then the rows have three records each which is how it should be…
Sorry you are using mysql_query() which only returns a resource on success. instead do:
while ($row= mysql_fetch_array($result)) {
var_dump($row);
$username= $row["UserName"];
$realname= $row["RealName"];
$detail= $row["Detail"];
$imageavailable= $row["ImageAvailable"];
$imageunavailable= $row["ImageUnavailable"];
This should give you a better idea what is happening in your returned results. You can also do:
while ($row= mysql_fetch_array($result)) {
$username= $row["UserName"];
echo 'Username: '; echo var_dump($username) . '<br/>';
$realname= $row["RealName"];
echo 'Realname: '; echo var_dump($realname) . '<br/>';
$detail= $row["Detail"];
echo 'Detail: '; echo var_dump($detail) . '<br/>';
$imageavailable= $row["ImageAvailable"];
echo 'ImageAvailable: '; echo var_dump($imageavailable) . '<br/>';
$imageunavailable= $row["ImageUnavailable"];
echo 'ImageUnavailable: '; echo var_dump($imageunavailable) . '<br/>';
You need to see what is happening in row 1. Could it be that you have poorly escaped data returned in row 1?
Steve
Okay, this is the output from the first few records, all seems to be ok as far as I can tell…
array(10) { [0]=> string(9) "testlocal" ["UserName"]=> string(9) "testlocal" [1]=> string(9) "Test User" ["RealName"]=> string(9) "Test User" [2]=> string(25) "Testing, testing, testing" ["Detail"]=> string(25) "Testing, testing, testing" [3]=> string(14) "testlocal2.jpg" ["ImageAvailable"]=> string(14) "testlocal2.jpg" [4]=> string(14) "testlocal1.jpg" ["ImageUnavailable"]=> string(14) "testlocal1.jpg" }
array(10) { [0]=> string(5) "antje" ["UserName"]=> string(5) "antje" [1]=> string(5) "Antje" ["RealName"]=> string(5) "Antje" [2]=> string(25) "Testing, testing, testing" ["Detail"]=> string(25) "Testing, testing, testing" [3]=> string(13) "AntjeText.jpg" ["ImageAvailable"]=> string(13) "AntjeText.jpg" [4]=> string(15) "AntjeNoText.jpg" ["ImageUnavailable"]=> string(15) "AntjeNoText.jpg" }
array(10) { [0]=> string(7) "Antonio" ["UserName"]=> string(7) "Antonio" [1]=> string(7) "Antonio" ["RealName"]=> string(7) "Antonio" [2]=> string(25) "Testing, testing, testing" ["Detail"]=> string(25) "Testing, testing, testing" [3]=> string(15) "AntonioText.jpg" ["ImageAvailable"]=> string(15) "AntonioText.jpg" [4]=> string(17) "AntonioNoText.jpg" ["ImageUnavailable"]=> string(17) "AntonioNoText.jpg" }
And then it pretty much continues on like that through all the records…
I also change the query so that it lists the records by RealName, so the first row is now a different set of records- but the same thing happens regardless, only 1 record gets output in the first row…
Okay, just changed a few users so that for a specific date they become “Available” (and therefore their “Available” image shows, and it breaks the row output completely- it displays as one long single row with all the records in it!
If I then select another date when no one is available, it goes back to rows of three (except the first row of course, which only contains 1 record)
Really bizarre- the available and unavailable images are all the same size so it can’t be a physical size / table format issue as far as I can tell.???
Actually looking at it, if I set the date so that ONE user is available, their image shows and it doesn’t break the table row format. But if TWO users are both “available” then not only does it output as one single row, but it doesn’t show their Available images- it shows their Unavailable images!
Ok, lets simplify:
include ('inc/dbconnect.php');
// Initialize variables;$type = null;
$date = null;$daynight = null;
if($_POST['checkavailability']) {
$type=@$_POST['_Type'];
$type = htmlentities($type);
$date=@$_POST['_DateSelected'];
$date = htmlentities($date);
$daynight=@$_POST['_Time'];
$daynight = htmlentities($daynight);
// Build SQL Query
// specify the table and field names for the SQL query
$query = null;
$query = "
SELECT
RealName
, Detail
, ImageAvailable
, ImageUnavailable
FROM
users";
$numresults = null;
$numrows = null;
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// get results $results = null;
$result = mysql_query($query) or die("Couldn't execute query");
echo '<table cellpadding="0" cellspacing="0" width="700" border="0"><tr>';
// Initialize variables;
$userName = null;
$realName = null;
$detail = null;
$imageAvailable = null;
$imageUnavailable = null;
$row = null;
// display the results returned
while ($row= mysql_fetch_array($result)) {
$userName = html_entity_decode($row["UserName"]);
$realName = html_entity_decode($row["RealName"]);
$detail= html_entity_decode($row["Detail"]);
$imageAvailable = html_entity_decode($row["ImageAvailable"]);
$imageUnavailable = html_entity_decode($row["ImageUnavailable"]);
echo '<td align="left" width="200">' . $realname. '<br>' . $detail . '</td>';
if ($availableuser == $username){
echo '<td><img src="images/' . $imageAvailable . '" width="100" height="150"></td>';
} else if (!$username == $availableuser){
echo '<td><img src="images/' . $imageUnavailable . '" width="100" height="150"></td>';
}
}
echo '</tr></table>'; }
Does this output all the rows correctly? I have put in a few best practices such as initializing variables and escaping input and output - presumably you have escaped the user data when it is entered into the database?
Steve
I bet this can be done only using single query but for now the following should be the better option:
if($_POST['checkavailability'])
{
$type = mysql_real_escape_string($_POST['_Type']);
$date = mysql_real_escape_string($_POST['_DateSelected']);
$daynight = mysql_real_escape_string($_POST['_Time']);
// Build SQL Query
$query = "SELECT UserName, RealName, Detail, ImageAvailable, ImageUnavailable FROM users";
$result = mysql_query($query) or die('Error on query: ' . mysql_error());
$numrows = mysql_num_rows($result);
echo '<table cellpadding="10" cellspacing="10" width="700" border="0"><tr>';
// display the results returned
$counter = 1;
while ($row = mysql_fetch_array($result)){
$available = false;
$_query = "SELECT UserName FROM dates WHERE DateAvailable='" . $date . "' AND DayNight='" . $daynight . "' AND UserName='" . $row["UserName"] . "'";
$_result = mysql_query($_query);
if($_result && mysql_num_rows($_result) >= 1){
$available = true;
}
echo '<td>
<img src="images/' . (($available) ? $row["ImageAvailable"] : $row["ImageUnavailable"]) . '" width="150" height="150"><br />
<strong>' . $row["RealName"] . '</strong><br />' . $row["Detail"] . '
</td>';
if($counter % 3 == 0){
echo '</tr>
<tr>';
}
$counter++;
}
echo '</tr>
</table>';
}
Hi Raju- well, that DOES output the rows as threre records for each row, unfortunately what happens is that the first half of the records / users shows as Unavailable and then the next half show as Available for some reason- not sure why?
Actually thinking about it I’m not sure it could work for me, as I also want to have “available” users be clickable (but unavailable ones should not be clickable), i.e there has to be a conditional specifically for them, where the image is output wrapped in a link tag- it then takes people to the user’s “full details” page (if they’re available)