While loop confusion ?

[Accurax]

I have a page that i want to display all of my users...

first of all it should search the "members" database and collect each users username, then it should search the "pictures" database and collect each members picture.

Problem is, it doesnt seem to work correctly, and im getting some very odd results, rangeing from displaying each username 13 times before starting to display the next username, and only showing the first user in the tables picture instead of the corrcect ones.... depending on how i have it structured it also seems to miss the first user in the database completely.

heres the code as it is atm

[code]
<?php
$user_query = "SELECT * FROM members, pictures";
$result = mysql_query($user_query)
or die ("no can do");
$row = mysql_fetch_array($result);

while ( $row = mysql_fetch_array($result))
{
echo '<a href="view_profile.php?username='.$row['username'].'">'; echo $row['username']; echo "</a>";
echo "<br>";
echo "<ul id='thumbs'><li><a href='view_profile.php?username=".$row['username']."'><img src='".$row['picture']. "'></a></li></ul>";
}
?>
[/code]

The above code seems to work, except that every user is displayed multiple times down the page b4 it moves onto the next user.

Has anyone got any idea's??

[play_]

can't see why it's doing that.
Maybe there's a problem with the script thats inserts the members into the database, and you're inserting them multiple times?

[emehrkay]

im pretty sure its because you dont make a connection between the user and picture table

SELECT * FROM members m INNER JOIN pictures p ON p.member_id = m.member_id

thats if they both contain a key field of member_id

[Accurax]

i considered that, but no, the inserion script seems to be fine, i physically checked the database aswell, and it all seems fine .... if i take the query to the "pictures" tabe out of the code then i end up with the following:

[code]
<?php
$user_query = "SELECT * FROM members";
$result = mysql_query($user_query)
or die ("no can do");
$row = mysql_fetch_array($result);

while ( $row = mysql_fetch_array($result))
{
echo '<a href="view_profile.php?username='.$row['username'].'">'; echo $row['username']; echo "</a>";
echo "<br>";
echo "<ul id='thumbs'><li><a href='view_profile.php?username=".$row['username']."'><img src='".$row['picture']. "'></a></li></ul>";
}
?>
[/code]

No, obviously this code will display broken images, as its not querying the table.

However, the interesting thisn is that the above code displays all the usernames correctly .... apart from the first user in the tabel, which it ignores for some reason.

FYI the code in the OP does display the first user in the table... all b it 17 times

I'm so confused, does anyone have any idea's?

[Accurax]

[quote author=emehrkay link=topic=122095.msg503092#msg503092 date=1168612144]
im pretty sure its because you dont make a connection between the user and picture table

SELECT * FROM members m INNER JOIN pictures p ON p.member_id = m.member_id

thats if they both contain a key field of member_id
[/quote]


What do the m and the p do btw?

[emehrkay]

[quote author=Accurax link=topic=122095.msg503097#msg503097 date=1168612358]
[quote author=emehrkay link=topic=122095.msg503092#msg503092 date=1168612144]
im pretty sure its because you dont make a connection between the user and picture table

SELECT * FROM members m INNER JOIN pictures p ON p.member_id = m.member_id

thats if they both contain a key field of member_id
[/quote]


What do the m and the p do btw?
[/quote]

aliases, its like saying AS. so that you dont have to type WHERE members.member_id = pictures.picture_id

you define $row outside of the loop, print_r($row) and see what your array looks like before the loop

[Accurax]

ahhh i think i get you

that works really well mate thankyou =)

sorry to keep bugging you with this, but it still doesnt display the first record in the database ..... have you any idea why?

[emehrkay]

[quote author=Accurax link=topic=122095.msg503111#msg503111 date=1168612754]
ahhh i think i get you

that works really well mate thankyou =)

sorry to keep bugging you with this, but it still doesnt display the first record in the database ..... have you any idea why?
[/quote]

are you using the query i gave you with the INNER JOIN?

if you are, it may be becasue the picture table may not have a member_id that matches the first row of the member table. Change it to LEFT JOIN and see what happens

[Accurax]

Edit :

Sorry LEFT JOIN doesnt seem to do it either ?

???

[Accurax]

But yes im using the query that you suggested :)

[boo_lolly]

[quote author=Accurax link=topic=122095.msg503111#msg503111 date=1168612754]
ahhh i think i get you

that works really well mate thankyou =)

sorry to keep bugging you with this, but it still doesnt display the first record in the database ..... have you any idea why?
[/quote]

do you have "$row = mysql_fetch_array($result)" anywhere in your code above your while loop?

[Accurax]

[code]
<?php
$user_query = "SELECT * FROM members m INNER JOIN pictures p ON p.user_name = m.user_name";

$result = mysql_query($user_query)
or die ("no can do");
$row = mysql_fetch_array($result);

while ( $row = mysql_fetch_array($result))
{
echo '<a href="view_profile.php?user_name='.$row['user_name'].'">'; echo $row['user_name']; echo "</a>";
echo "<br>";
echo "<ul id='search_thumbs'><li><a href='viewprofile.php?user_name=".$row['user_name']."'><img src='../".$row['picture1']. "'></a></li></ul>";
}
?>[/code]

Yes i do ... is that wrong?

[Accurax]

Shameless bump ... I still cant figure this out im afraid .... trying to see why the script ignores the first entry in the table

[kobmat]

[CODE]
<?php
$user_query = "SELECT * FROM members m INNER JOIN pictures p ON p.user_name = m.user_name";

$result = mysql_query($user_query)
or die ("no can do");
//$row = mysql_fetch_array($result); -- I commented this out this is causing your problem

while ( $row = mysql_fetch_array($result))
{
echo '<a href="view_profile.php?user_name='.$row['user_name'].'">'; echo $row['user_name']; echo "</a>";
echo "<br>";
echo "<ul id='search_thumbs'><li><a href='viewprofile.php?user_name=".$row['user_name']."'><img src='../".$row['picture1']. "'></a></li></ul>";
}
?>
[/CODE]

[Ninjakreborn]

if you have $row called above for another query, then here, you are overwriting the other, could that be part of your problem.
It seems confusing, I never had that kind of problem with a query before.
must be very frustrating.

[Accurax]

Kobmat i think i love you ..... thankyou so much mate, this is basically the start of a search system im building, and its right on the limit of my current abilities, im learning fast, but i have a feeling i may have some more questions before too long.

Thankyou so much, removing the $row = mysql_fetch_array($result); was all that was needed.
[me=Accurax]breathes a sigh of relief[/me]

[boo_lolly]

[quote author=Accurax link=topic=122095.msg503162#msg503162 date=1168615840]
[code]
<?php
$user_query = "SELECT * FROM members m INNER JOIN pictures p ON p.user_name = m.user_name";

$result = mysql_query($user_query)
or die ("no can do");
$row = mysql_fetch_array($result);

while ( $row = mysql_fetch_array($result))
{
echo '<a href="view_profile.php?user_name='.$row['user_name'].'">'; echo $row['user_name']; echo "</a>";
echo "<br>";
echo "<ul id='search_thumbs'><li><a href='viewprofile.php?user_name=".$row['user_name']."'><img src='../".$row['picture1']. "'></a></li></ul>";
}
?>[/code]

Yes i do ... is that wrong?
[/quote]
if you have "$row = mysql_fetch_array($result)" somewhere above your while loop, that's the reason why it's not bringing up the first result in your while loop. because it is already being called once before your while loop. try taking that line out, and see what happens.