Retrieving Mysql into web table

[aktell]

Hi there,

 

I’m in need of some real help please.

Most likely this is nothing mayor for somebody who knows PhP well, unfortunately I’m not one of them yet.

OK, I’m trying to simply retrieve records from a table in Mysql. Say I have around 60 to 110 rows in a table of different members each with a (FirstName, LastName and Contact) field, and I would like to show them all on a web page.

As it is the first time I’m trying myself with table in PhP my question is, am I’m doing it right ??? Do I use the markers around the <br>, <div> and other tags in the right way ???

 

My code I have shown below includes several different code’s which looked to me promising, well if somebody could help here so I can actually retrieve information from a specific table.

 

Many thanks in advance to the person/s trying to help.

 

aktell

 

<?php 

echo "<p align=\"center\">"; 

$sort_order = 'ASC'; 
$order_by = 'FirstName'; 
$db_tablename = 'memberdetails'; 

$db_host = 'localhost'; 
$user = 'user'; 
$password = 'password'; 
$db_name = 'contactdetails'; 

// Connect to Myqsl. 
$link = mysql_connect('$db_host', '$user', '$password', '$db_name'); 
    if (!$link) { 
       die('Could not connect to Mysql: ' . mysql_error()); 
} 
//************ 
//      // Check if connection was a success. (Ccould the <div> be use as below. ???) 
//      if (!$link){ 
//        echo '<div>Can not connect to database: '.mysql_error().' </div>'; 
//        die; 
//      } 
//************ 

mysql_close($link); 

$result = mysql("$db_name","SELECT FirstName, LastName, Contacts FROM $db_tablename $order_by $sort_order") or die(mysql_error()); 

//************ 
// Query. 
//$sql = ("SELECT FirstName, LastName, Contacts FROM $db_tablename $order_by $sort_order"); 
// 
// Perform Query. 
//$result = mysql_query($sql); 
//   if(!$result) error_message(sql_error()); 
//************ 

// Set table. 
echo  ('<TR>');   
echo  ('<TD vAlign=\"top\" width=\"990\">');  
echo  ('<TABLE cellSpacing=\"0\" cellPadding=\"6\" width=\"90%\">'); 
echo  ('<TR>'); 
echo  ('<TD vAlign=\"top\" width=\"80%\">');  
echo  ('<p> </p>'); 
echo  ('<p> </p>'); 
echo  ('<br>');  
echo  ('<table width=\"535\" align=\"center\" cellPadding=\"2\" cellSpacing=\"2\">'); 
echo  ('<tr>'); 
echo  ('<td>'); 
// Table header. 
echo  ('<div align="center"><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"4\" color=\"#FFFFFF\">Contact List:</font></div><br>'); 
echo  ('<br>'); 
echo  ('<table width=\"500\" border=\"1\" borderColorLight=\"#000000\" cellPadding=\"2\" cellSpacing=\"0\">'); 

// This way. ??? ******************************************************************************************************************** ??? 
while ($row = mysql_fetch_assoc($result)) { 
echo  ('<tr bgcolor=\"#CCCCCC\">'); 
echo  ('<td align=\"center\" width=\"33%\" bgcolor=\"#999999\"> echo $row['FirstName']; </td>'); // Or maybe - echo .$row0[0]. - ??? 
echo  ('<td align=\"center\" width=\"33%\" bgcolor=\"#999999\"> echo $row['LastName']; </td>');  // Or maybe - echo .$row0[1]. - ??? 
echo  ('<td align=\"center\" width=\"33%\" bgcolor=\"#999999\"> echo $row['Contacts']; </td>');  // Or maybe - echo .$row0[2]. - ??? 
echo  ('</tr>'); 

echo  ('<tr bgcolor=\"#CCCCCC\">'); 
echo  ('<td align=\"center\" width=\"25%\" bgcolor=\"#999999\"> echo $row['FirstName']; </td>'); 
echo  ('<td align=\"center\" width=\"25%\" bgcolor=\"#999999\"> echo $row['LastName']; </td>'); 
echo  ('<td align=\"center\" width=\"25%\" bgcolor=\"#999999\"> echo $row['LastName']; </td>'); 
echo  ('</tr>'); 

// Or this way. ??? **************************************************************************************************************** ??? 
$query_data = mysql_fetch_array($result); { 

$firstname = $query_data["FirstName"]; // ??? 
$lastname = $query_data["LastName"]; // ??? 
$contacts = $query_data["Contacts"]; // ??? 

echo  ('<tr bgcolor=\"#CCCCCC\">'); 
echo  ('<td align=\"center\" width=\"33%\" bgcolor=\"#999999\"><?php echo $firstname; ?></td>'); // Or maybe - <?php echo $firstname = $query_data["FirstName"]; ?> - ??? 
echo  ('<td align=\"center\" width=\"33%\" bgcolor=\"#999999\"><?php echo $lastname; ?></td>');  // Or maybe - <?php echo $lastname = $query_data["LastName"]; ?> - ??? 
echo  ('<td align=\"center\" width=\"33%\" bgcolor=\"#999999\"><?php echo $contacts; ?></td>');  // Or maybe - <?php echo $contacts = $query_data["Contacts"]; ?> - ??? 
echo  ('</tr>'); 

echo  ('<tr bgcolor=\"#CCCCCC\">'); 
echo  ('<td align=\"center\" width=\"25%\" bgcolor=\"#999999\"><?php echo $firstname; ?></td>'); 
echo  ('<td align=\"center\" width=\"25%\" bgcolor=\"#999999\"><?php echo $lastname; ?></td>'); 
echo  ('<td align=\"center\" width=\"25%\" bgcolor=\"#999999\"><?php echo $lastname; ?></td>'); 
echo  ('</tr>'); 

echo  ('</table>'); 
echo  ('</td>'); 
echo  ('</tr>'); 
echo  ('</table>'); 
} 
} 

mysql_free_result($result); 

?> 


Below some other ideas ! But I would not know how to addapt IF. 
********************************************************************************* 

<?php 
include "./common_db.inc"; 
$link_id = db_connect(); 
$result = mysql_list_fields("sample_db", "user", $link_id); 

for($i=0; $i < mysql_num_fields($result); $i++ ) { 
   echo mysql_field_name($result,$i ); 
   echo "(" .  mysql_field_len($result, $i) . ")"; 
   echo " - " . mysql_field_type($result, $i) . "<BR>"; 
   echo " " . mysql_field_flags($result, $i) . "<BR>"; 
} 
********************************************************************************* 

while ($row = mysql_fetch_row($result)) { 

// added code to use the tablename and select all records from that table 
echo ('Table ' . $row[0] . 'br /><table>'); 
$sql="select * from " . $row[0]; 

$result2 = mysql_query($sql) or die ('SQL problem selecting from table'); 
if (mysql_num_rows($result2) >= 1){ 
echo ('<tr>');  

while ($row2 = mysql_fetch_row($result2)) { 
for ($i=0; $i<mysql_num_fields($result2), $i ++){ 
echo ('td>' . $row2[$i] . '</td>'); 
} 
} 
echo ('</tr>');  
} else { 
echo ('<tr><td colspan="999">No records for table' . $row[0] . '</td></tr>');  
} 
echo ('</table>'); 

mysql_free_result($result2); 
} 
********************************************************************************* 

[artacus]

question is, am I’m doing it right

 

The answer is no.

[fenway]

Perhaps this would be better addressed in the PHP help section first... it's hard to even find the sql in there.

[artacus]

First get your query working.

1) Separate the code for making the connection and store it in an include file because you'll be using that in all of your pages.

2) Store your query as a variable ($query = "SELECT * FROM Contacts WHERE whatever = 1")

3) Run the query ($result = mysql_query($query) or die(mysql_error())

4) Iterate thru your results ( while ($row = mysql_fetch_assoc($result)) { ...)

 

Now for the display part

1) Separate the logic from the display as much as you can. It absolutely drives me crazy when I have to make sense of code like this:

}

echo ('</tr>'); 

} else {

echo ('<tr><td colspan="999">No records for table' . $row[0] . '</td></tr>'); 

}