Repeating Code for Each Entry in the SQL Database

[Akenatehm]

Hey Guys,

 

I am trying to make a script that will repeat a block of code for each entry in the database and fill each one with the fields of the respective rows.

 

Here's the part of the code that I think is neccessary:

<?php
$getitems = mysql_query("SELECT itemid FROM rewards");

if(!$getitems)
{
die('Failed to Get Items') . mysql_error();
}
else
{
while($items = mysql_fetch_array($getitems, MYSQL_ASSOC))
{
$allitems = $items['itemid'];
}
$searchitems = mysql_query("SELECT * FROM items WHERE entry = '$allitems'");
if(!$searchitems)


{
die('Could Not Search for Items' . mysql_error());
}
else
{
while($allrows = mysql_fetch_array($searchitems, MYSQL_ASSOC))
{
$numrows = mysql_num_rows($searchitems);
$name1 = $allrows['name1'];
$displayid = $allrows['displayid'];
$requiredlevel = $allrows['requiredlevel'];
$unique = $allrows['Unique'];
$quality = $allrows['quality'];
$description = $allrows['description'];

}
$i = 0;
while ($i < $num){
echo "Name: $name1<br /> Display ID:$displayid<br /> Required Level: $requiredlevel <br /> Unique: $unique <br /> Quality: $quality <br /> Description: $description <br /> ";
}
}
}
?>

[corbin]

So uh...  What exactly is the problem?

[Cosizzle]

Could you not put your

echo "Name: $name1<br /> Display ID:$displayid<br /> Required Level: $requiredlevel <br /> Unique: $unique <br /> Quality: $quality <br /> Description: $description <br /> ";

 

Into the loop thats grabbing the info?

while($allrows = mysql_fetch_array($searchitems, MYSQL_ASSOC))
{
$numrows = mysql_num_rows($searchitems);
$name1 = $allrows['name1'];
$displayid = $allrows['displayid'];
$requiredlevel = $allrows['requiredlevel'];
$unique = $allrows['Unique'];
$quality = $allrows['quality'];
$description = $allrows['description'];

echo "Name: $name1<br /> Display ID:$displayid<br /> Required Level: $requiredlevel <br /> Unique: $unique <br /> Quality: $quality <br /> Description: $description <br /> ";
}

 

[Akenatehm]

@corbin Well. It is only showing one record.

 

@Cosizzle The while loop it is in is counting how many times it needs to re write it. So I don't know how to transfer that across.

 

Any ideas?

[Akenatehm]

Anyone know how to do this?

[premiso]

Try this:

 

$allitems = array();   
while($items = mysql_fetch_array($getitems, MYSQL_ASSOC))
{
    $allitems[] = $items['itemid'];
}
$items = implode(', ', $allitems);
$searchitems = mysql_query("SELECT * FROM items WHERE entry IN( $allitems)");

 

And see if that solves your issue.

[Akenatehm]

Here's the code in the area that's not working:

$getitems = mysql_query("SELECT itemid FROM rewards");

if(!$getitems)
{
die('Failed to Get Items') . mysql_error();
}
else
{
$allitems = array();   
while($items = mysql_fetch_array($getitems, MYSQL_ASSOC))
{
    $allitems[] = $items['itemid'];
}
$items = implode(', ', $allitems);
$searchitems = mysql_query("SELECT * FROM items WHERE entry IN( $allitems)");
if(!$searchitems)


{
die('Could Not Search for Items' . mysql_error());

 

After changing what you suggeted I now get these errors:

Could Not Search for ItemsUnknown column 'Array' in 'where clause'

[premiso]

Sorry, small change:

 

$allitems = array();   
while($items = mysql_fetch_array($getitems, MYSQL_ASSOC))
{
    $allitems[] = $items['itemid'];
}
$allitems = implode(', ', $allitems); // changed this to allitems
$searchitems = mysql_query("SELECT * FROM items WHERE entry IN( $allitems)");

[Akenatehm]

Ok, I changed that. Now the page just shows up blank.

 

Code Update:

<?php

$host = "localhost";
$name = "username";
$pass = "pass";
$db = "db";

$con = mysql_connect($host,$name,$pass);
if (!$con)
{
die('Failed to Connect:' . mysql_error());
}

$selectdb = mysql_select_db($db);
if (!$selectdb)
{
die('Failed to Select Database:' . mysql_error());
}

$getitems = mysql_query("SELECT itemid FROM rewards");

if(!$getitems)
{
die('Failed to Get Items') . mysql_error();
}
else
{
$allitems = array();   
while($items = mysql_fetch_array($getitems, MYSQL_ASSOC))
{
    $allitems[] = $items['itemid'];
}
$allitems = implode(', ', $allitems); // changed this to allitems
$searchitems = mysql_query("SELECT * FROM items WHERE entry IN( $allitems)");

if(!$searchitems)
{
die('Could Not Search for Items' . mysql_error());
}
else
{
while($allrows = mysql_fetch_array($searchitems, MYSQL_ASSOC))
{
$numrows = mysql_num_rows($searchitems);
$name1 = $allrows['name1'];
$displayid = $allrows['displayid'];
$requiredlevel = $allrows['requiredlevel'];
$unique = $allrows['Unique'];
$quality = $allrows['quality'];
$description = $allrows['description'];

}
$i = 0;
while ($i < $num){
echo "Name: $name1<br /> Display ID:$displayid<br /> Required Level: $requiredlevel <br /> Unique: $unique <br /> Quality: $quality <br /> Description: $description <br /> ";
}
}
}
?>

[premiso]

You are not setting $num to be anything for the last while statement. Since it would be looked at as "0" this is never ran.

 

What is $num support to be?

 

Also

 

   $description = $allrows['description'];

 

Each time the loop is ran that is overwritten. Why not move the data from the third loop into the second...

 

while($allrows = mysql_fetch_array($searchitems, MYSQL_ASSOC))
{
   $numrows = mysql_num_rows($searchitems);
   $name1 = $allrows['name1'];
   $displayid = $allrows['displayid'];
   $requiredlevel = $allrows['requiredlevel'];
   $unique = $allrows['Unique'];
   $quality = $allrows['quality'];
   $description = $allrows['description'];
echo "Name: $name1<br /> Display ID:$displayid<br /> Required Level: $requiredlevel <br /> Unique: $unique <br /> Quality: $quality <br /> Description: $description <br /> ";
}

 

Give that a try and see what comes of it.

[Akenatehm]

Sweet, thanks very much. Can you show me in comments what all these new editions are doing. I like to learn how things work.

 

It works great.!! Thanks!

[premiso]

The first, since you were just dong $allitems = $rowentry  you were assigning that a new variable each time. You expected it to keep the results, but the = is an assignment operator, so it was being overwritten.

 

What I did there was make $allitems and array so we could store each entry as an element of an array then I used the implode outside of the loop to combine the elements of an array with a , spliiting them. This was because the IN function in my sql can take multiple items as long as they are split by a comma. This allowed for the search to actually works, since you will not just be searching the last item obtained from the first sql, instead it pulled where all the IDs were in the $allitems.

 

Next, moving the echo inside the first while, is more or less just common coding. Why have 2 loops when one will suffice. Since you were not doing anything else with the return from the DB but echoing it was better/easier just to put that inside that while loop.