SELECT query is only SELECTING the first row, how do I SELECT all

PHP
1

I have this simple query:

Code:
$q5 = "select listingid FROM userlisting WHERE userid = '$_SESSION[UserID]'";

$r5 = mysql_query($q5) or die(mysql_error());
$a5 = mysql_fetch_array($r5);
The userlisting table is a many to many relationship. It is used
as a lookup table. Each userid can be associated with multiple
listingids and vice versa.

Also in my file (a html template file) I have this code

Code:
if(!empty($_SESSION[UserID]) and $a5['listingid'] == $_GET['id']) :

So I am wanting to check if the listingid column in userlisting
table = the id of the page (well, it is a property website and it is the
id of the property). As each user can be associated with many
listingids I need to be able to check multiple rows. But for some reason
it only checks the very first row.

In the userlisting table there is the following test entries: userid | listingid 1 1 1 2

So one user associated to two listingids. However, it is acting like
this userid is only associated with listingid 1, the very first row.

How would I resolve this please?

2

run your query outside of php, directly in the database, to see what it’s actually doing – my guess is that your php code is faulty

3

I think [UserID] in the brackets needs to be inside some sort of quotes?

4

I’ve moved this to the PHP forum

$_SESSION[UserID] should be $_SESSION[‘UserID’] you’re missing the single quotes.

You need to be migrating away from the mysql_* extension as it’s deprecated in version 5.5 of PHP and is being removed from version 7 of PHP. You should be using either the mysqli_* extension or PDO. You should get into the habit of using prepared statements whenever you’re using a variable in a query, not matter where the value in the variable originated from

5

So four things.
1: What you are describing in the code is not technically a many-to-many relationship; it’s one side of a many-to-many relationship represented as two one-to-many relationships. Specifically, that one userid can have many listings.

2: Your select query is selecting all of the rows, and returning them.

3: Your code isnt actually checking for multiple rows. fetch_array retrieves a single row from the result set. To go through the many rows, you will have to do a [FPHP]while[/FPHP] loop through the rows. Take a look at the examples in the manual.

4: As spacephoenix has pointed out, move away from mysql_ and start either with mysqli_ or PDO.

6

Hi

I already tried a while loop but did not work:

$q5 = “select listingid FROM userlisting WHERE userid = ‘$_SESSION[UserID]’”;
$r5 = mysql_query($q5) or die(mysql_error());
while($a5 = mysql_fetch_array($r5)) {

if($a5['listingid'] == $_GET['id']) {
    $ShowContactInfo = 'True';
}
    
else {
        $ShowContactInfo = 'False';
        }

}

7

Well your structure at least looks sound now.

Is userid in the userlisting table an INT or a VARCHAR?
What do you get if you put inside your while loop (just after the { )

var_dump($a5);
8

Thank you for the reply.

userid int(11)
listingid int(11)

Should I put the code you suggested after: while($a5 = mysql_fetch_array($r5)) {

9

Yes, you should.

If userid is an INT, why are you putting quotes around it in your query? you dont quote numbers.

Should look like

$q5 = "select listingid FROM userlisting WHERE userid = ".$_SESSION['UserID'];
10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.