PHP & MySQL , Fetch table

Question

I have to make this code return the same results but only where userID is a number (ex 16); Every device has an userid and an username in this table. This is the code :

<?php 

$myQuery= 'SELECT * FROM devicesissued di LEFT JOIN usercred uc ON di.userID = uc.userID WHERE 1 ORDER BY timeStamp DESC';
$result = mysql_query($myQuery) or die($myQuery."<br/><br/>".mysql_error());
while($row = mysql_fetch_array($result)) 
{
    $catQuery = 'SELECT * FROM deviceinventory WHERE deviceID = '. $row['deviceID'];
    $catResult = mysql_query($catQuery) or die($catQuery."<br/><br/>".mysql_error());
    $cat = mysql_fetch_array($catResult);
  echo "<tr>";
  echo "<td>" . $row['displayName'] . "</td>";
  echo "<td>" . $row['deviceID'] . "</td>";
  echo "<td>" . $cat['barCodeID'] . "</td>";
  echo "<td>" . $cat['category'] . "</td>";
  echo "<td>" . $row['deviceName'] . "</td>";
  echo "<td>" . $cat['operatingSystem'] . "</td>";
  echo "<td>" . $cat['serialNumber'] . "</td>";
  echo "<td>" . $cat['macAddress'] . "</td>";
  echo "<td>" . $row['timeStamp'] . "</td>";
  echo "</tr>";
}

?>

If I add the WHERE 'userID' = A-Number clause it doesn't do anything.

If you have any ideas I will be glad to try them.

Thank you.

Are you really adding WHERE 'userID' with quotes? That means you are comparing the string with ID, not a column value. The query should be SELECT * FROM devicesissued di LEFT JOIN usercred uc ON di.userID = uc.userID WHERE uc.userId = 16 ORDER BY timeStamp DESC — Finwe
– Finwe, Commented Dec 6, 2016 at 16:57
@Thamaraiselvam WHERE 1 in query is optional. It doesn't do anything much. It produces the same effect as without it will produce. Also, like you asked why it should be there, he could remove it to avoid confusions. — Wolverine
– Wolverine, Commented Dec 6, 2016 at 16:59
Replace WHERE 1 with WHERE di.userID = A-Number or if you have any good reason (which I can't imagine to be honest) for WHERE 1 you have to say WHERE 1 AND di.userID = A-Number. And as @Hackerman already said - do yourself a favor and don't use mysql extension. — Andreas Schrammel
– Andreas Schrammel, Commented Dec 6, 2016 at 17:08

Marcin Orlowski · Accepted Answer · 2016-12-06 17:15:46Z

1

Try to change your query a little bit

$myQuery= 'SELECT * FROM devicesissued di 
             LEFT JOIN usercred uc ON di.userID = uc.userID 
             WHERE di.userID=the_number_here ORDER BY timeStamp DESC';

As you have the userID in both joined tables (deviceissued and usercred), you have to tell MySQL on which table you want to perform the WHERE-clause.

edited Dec 6, 2016 at 17:15

Marcin Orlowski

75.7k11 gold badges128 silver badges152 bronze badges

answered Dec 6, 2016 at 17:12

Andreas Schrammel

4631 gold badge6 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP & MySQL , Fetch table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related