Making A Query Loop...

[northerncomfort]

I've encountered a bit of a problem with my contacts database. I'm building a contacts database for my office, which is working pretty nicely so far. The actual contacts part is working perfectly, and is based in mySQL. However, I've also added a "My Contacts" as this was a big request in the office. It uses a simple login, takes the name, creates a table in the database called $username_contacts, etc. Adding a contact to the mycontacts table has been working nicely so far, but I'm having a problem with listing the "My Contacts" part that hopefully I can find some help here with.

Basically, each contact in the main contacts table has an id number. The $username_contacts table keeps track of who you have in your contacts list by the id number.

Here is the code in question. It first checks to make sure you're logged in, then puts your username as $name and grabs the correct table. Then it sees how many contacts you have in my contacts (this is where the script starts to flounder), and for each contact you have in my contacts, it grabs the proper contact info from the main contacts table. However, that isn't working so nicely at the moment, and hopefully somebody here with a beady eye will be able to spot the problem.

Also, is there any way to keep each contact email address in a different variable so I could have an option to display a table of the email addresses so that the folks in the office could quickly email all of "my contacts" at once?

[code]<?php
if($logged_in){
echo "My Contacts";
include "database.php";
$name = $_SESSION['username'];
mysql_connect($connect,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$sql2 = "SELECT * FROM ".$name."_contacts";
$result2 = mysql_query($sql2);
$num=mysql_numrows($result2);
$i = 1;
while ($i <= $num){
$toget = mysql_result($result2,$i,"contact_id");
$sql = "select * from contacts where id='".$toget."'";
$result = mysql_query($sql);
$first=mysql_result($result,$i,"firstname");
$last=mysql_result($result,$i,"lastname");
$company=mysql_result($result,$i,"company");
$address=mysql_result($result,$i,"address");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
$zip=mysql_result($result,$i,"zip");
$fax=mysql_result($result,$i,"fax");
$workphone1=mysql_result($result,$i,"workphone1");
$workphone2=mysql_result($result,$i,"workphone2");
$homephone=mysql_result($result,$i,"homephone");
$cellphone=mysql_result($result,$i,"cellphone");
$email=mysql_result($result,$i,"email");
$id=mysql_result($result,$i,"id");
echo "All the usual contact table stuff goes here, it works fine but is bulky so I editted it out for your sake...";
$i++;
}
}
else{
   echo "You are not logged in. You need to be logged in to use My Contacts.<br /><a href=\"index.php\">Log In</a>";
}
?>[/code]

[joquius]

first off, you wanna change that long list of mysql_results to this

while ($data = mysql_fetch_array ($result))
{
$field = $data['field']; // and all other stuff which was in the for() loop
}

actually...should look like this:

[code]
<?php
if ($logged_in)
{
echo "My Contacts";

include "database.php";

$name = $_SESSION['username'];

mysql_connect ($connect, $username, $password);
mysql_select_db ($database) or die( "Unable to select database");

$sql = "SELECT * FROM ".$name."_contacts";
$result = mysql_query ($sql) or die (mysql_error());
while ($data = mysql_fetch_array ($result))
{
    $first = $data['firstname']; //etc
}
}
else
{
   echo "You are not logged in. You need to be logged in to use My Contacts.<br /><a href=\"index.php\">Log In</a>";
}
?>
[/code]

[Barand]

try
[code]
$sql2 = "SELECT * FROM ".$name."_contacts";
$result2 = mysql_query($sql2);

$all_contacts = array();
while ($row = mysql_fetch_assoc($result2)) {
    extract($row);
    $all_contacts[] = $email;
}
   // put all email addresses into comma-delimited string
$to_all = join (',' , $all_contacts);[/code]

Why a separate table per user? Add username column then

SELECT * FROM contacts WHERE username = '$name';