[help] mysql data loop betwean differend tables

01/25/2012 12:35 PowerChaos#1

hi all
i was wondering if some1 know why this is refusing to work

Code:

$charshout = mysql_query("SELECT * FROM cq_user WHERE (`account_id` LIKE '".$_SESSION['id']."' )");
while ($user = mysql_fetch_assoc($charshout))
{
$i = $i+1;
$shoutpersonal[$i] = mysql_query("SELECT * FROM cq_ad_log WHERE (`user_name` LIKE '".$user['name']."') ORDER BY publish_time ASC LIMIT 5");
}

while($shouting = mysql_fetch_array($shoutpersonal[$i]))
{

the thing i try to do is to show all shouts from the characters

so the db need to do a loop to get all characters and then show the amount of characters

the data get taken from 2 tables , first you have account id that start searching for the character id ( as account_id == Character , and character_id == shouts )

so first i need to get the character id , and need to loop that until it got all characters

then it need to loop to show the shouts of all characters , limiting to 5 shouts and show the character name

so far it is partial working , for 1 of other reason the loop is not working like it need to be
it only shows the second character or no data if there is no second character

i was wondering if some1 could provide me a working code for that

if more code is needed for the example , feel free to ask and i shal release the full code of the page i am trying to get working

if there is no code/solution for this , then i just change the way the data get displayed :D (with a form , always works :D )

Greetings From PowerChaos

01/29/2012 08:58 funhacker#2

For starters I would only select the cq_user.id or both cq_user.id and cq_user.name, selecting all the data is pointless.

Now as I don't have navicat installed at the moment I couldn't go right into the cq_ad_log
But then I would create an array of strings using the following

select message from cq_ad_log where user_id == user_id;

Note that message is the field that ad log uses to save the actual broadcast message. Then user_id is the "external" key.

External Key:
Is a relationship between 2 tables, it most commonly is a one to many relationship which means in one table it will only have 1 occurrence (cq_user.id) and in another table it will have many occurrences (cq_ad_log.user_id).
The external key may be the user's name but as that's not the cq_user primary key it would be poor database design. So I'm assuming that they also have user id present in the ad log.

Then once you have the string array you could simply loop through the array's length like so

for (int messIndex = 0; messIndex < array.Length; messIndex++)
{
.....
}

This is all purely pseudo code as I know absolutely no php at this time. Also a tip, when you have a fixed length object that you would get from an array of answers, correct coding suggests you use a for loop.
Whiles are used for unknown lengths, like reading a text file line by line.

01/29/2012 15:40 PowerChaos#3

Thank you funhacker

only problem with cq_ad_log is that it got only 3 rows
cq_ad_log.name
cq_ad_log.message
cq_ad_log_date (no timestamp , normal date )

and name is indeed the primary key (bad coded)
i am not sure , but it should be possible that date is the unic key value (every min limit ?)

if date is the unic key then its a realy big problem as there is no way to find it back on date (as that is not a matched key betwean tables)

so i get something as those value's below

PHP Code:


			
$sesiion[id] = account.id after login (get stored as a sesion)
account.id = primary
cq_user.id  =  primary
cq_user.account_id = primary id from account.id
cq_user.name = primary for cq_ad_log.name
cq_ad_log.name = primary

because of that bad code do i need to get the name that match the id , as everything is working over a id in the other tables

the array is not realy possible to , as i cap the messages at 5 rows , but it need to match the names and it is not a unic value in cq_ad_log but a unic value in cq_users

so basicly if you got 5 differend characters then it need to loop until it got all 5 differend characters and show the top 5 from all 5 characters (cq_users.account_id )

if character 1 got 5 shouts and character 2 got 3 shouts , then it should show only the 5 latest shouts of both characters

2 latest messages is from char1 , when 3 latest messages is from char2 = 5 shouts from 2 differend chars showing under each other sorted on date

in meantime i fixed it with following code

PHP Code:


			
while ($charlist = mysql_fetch_assoc($chardata))
{
echo "<option value ='".$charlist['id']."'>".$charlist[name]."</option>";
}
?>
</select>
</table>
<br>
<input class=Butt type=submit value="See Character Stats" name="search">
</FORM>

and then based on id it get the name and stuff

but i still wonder why the below code works perfect in a loop when for the characters it doesnt :S

PHP Code:


			
while ($petinfo = mysql_fetch_assoc($petdata))
{
$petnames = mysql_query("SELECT * FROM cq_table WHERE (`id` LIKE '".$petinfo['table']."' )");
$petname = mysql_fetch_assoc($petnames);
echo ("<td>".$petname['table']."</td>");
}

it exact does what it need to do , it select the id from table X and then keeps looping and showing all names that match the eudemons primary key
and then it just shows the pets there name and it works perfect (its a 2 table hop , edited above code to provide example )

ayway , thank you for your solution and i am sure i can use it for other purposes now i got a idea how to make a loop on a differend way then using While

ps: it is still hard to make a control panel :P

Thank you
Greetings From PowerChaos