php mysql, How do I select from a table and place into an array?

@anothenJan 30.2006

Please bear with me as I am still trying to learn php and mysql.
I am using ver 4.0.26 of mysql. I also have been using phpMyadmin to create tables and some minor editing.
I have learned the basics of how to create edit and delete a table in mysql. I have a simple address book called “address_book”. The tables are ” index, name, address, phone”.
I am able to display the contents:
~~[URL=http://www.epourania.com/SAMSphp/16/select_test_reveal_all.php]~~Display address_book[/URL]
and the code is:

~~[quote]~~

<?php
// open the connection
$conn = mysql_connect(“localhost”, “xxxx” , “xxxx”);

// pick the database to use
mysql_select_db(“Big_File”,$conn);

// create the SQL statement
$sql = “SELECT * FROM address_book”;

// execute the SQL statement
$result = mysql_query($sql, $conn) or die(mysql_error());

//go through each row in the result set and display data
while ($newArray = mysql_fetch_array($result)) {
// give a name to the fields
// index, name, address, phone
// make $n the index number of arrays holding name address and phone

$index = $newArray[‘index’]; $name = $newArray[‘name’]; $address = $newArray[‘address’]; $phone = $newArray[‘phone’]; //echo the results onscreen echo “The index is $index name is $name address is $address phone is $phone <br>”;

};
echo “$result”;
?>

[/quote]

I intended to take the contents from the fields in the table and place each one in an arrays and take those contents and put them in an HTML table.

~~[list]~~

~~[*]~~

Is it possible to take the contents of the fields and place them in a php array?

~~[*]~~

Is it possible to use the variables from the php array and use them in HTML?

~~[*]~~

Is it possible to make HTML type of tables in php?

[/list]

The command that was used is: “.. while ($newArray = mysql_fetch_array($result)) {bla bla} ..”
I don’t fully understand the command ” mysql_fetch_array ” and when I echo the contents of ” $result ” it says “Resource id #2”

I can see this is a complex question, let me simplify:
I have a php file with an HTML table:

~~[quote]~~

<html>
<head>
<title>4×5 Table</title>
</head>
<body>

<!– Test Table –>

<!– BEGIN TABLE –>
<TABLE BORDER=”1″>
<TR>
<TD>

<!– LEFT SIDE TABLE 02 –>
<TABLE BORDER=”1″>

</TABLE>

</TD><TD>

<!– CENTER TABLE 02 –>

</TABLE>

</TD><TD>

<!– Right Table –>

</TABLE>

</TD><TD>

<!– RIGHT TABLE 02 –>

</TABLE>
</TD>
</TR>
</TABLE>
<!– END TABLE –>
</body>
</html>

[/quote]

it will display as follows:
~~[URL=http://www.epourania.com/SAMSphp/16/aTable.php]~~test table[/URL]

~~[list]~~

~~[*]~~

Is there a way I can take the values from the fields in the table of my mysql database, “address_book”, and place them in this HTML table?

~~[*]~~

or, Does php have commands similar to HTML tables?

[/list]

My whole purpose of is to get familiar with php and mySql. I am currently using the SAMS Teach Yourself book. Any recommendations would be greatly appreciated.

to post a comment

PHP

12 Comments(s) _↴

@LiL_aaronJan 30.2006 — #To make sure i understand what ya want...

from the table left side with be the id (index number)

and so on with the others...

Like...

1 - aaron - 111 me road - 1231 3221

2 - you - 112 me street - 3322 1125

ect ect

is that correct?

@LiL_aaronJan 30.2006 — #Anyways Try this:

[code=php]<?php
 // open the connection
 $conn = mysql_connect("localhost", "xxxx" , "xxxx");
 
 // pick the database to use
 mysql_select_db("Big_File",$conn);
 
 //Start of Table
 echo '<table border="1" width="50%"><tr>';
 echo '<td align="center"><b>ID</b></td>';
 echo '<td align="center"><b>Name</b></td>';
 echo '<td align="center"><b>Address</b></td>';
 echo '<td align="center"><b>Phone</b></td></tr>';
 
 // create the SQL statement
 $sql = "SELECT * FROM address_book";
 
 // execute the SQL statement
 $result = mysql_query($sql, $conn) or die(mysql_error());
 
 
 //go through each row in the result set and display data
 while ($newArray = mysql_fetch_array($result)) {
 // give a name to the fields
 // index, name, address, phone
 // make $n the index number of arrays holding name address and phone
 
 $index = $newArray['index'];
 $name = $newArray['name'];
 $address = $newArray['address'];
 $phone = $newArray['phone'];
 
 //Start Array Fields within the table
 
 echo '<tr>';
 echo '<td align="center"><b>$index</b></td>';
 echo '<td align="center"><b>$name</b></td>';
 echo '<td align="center"><b>$address</b></td>';
 echo '<td align="center"><b>$phone</b></td></tr>';
 };
 
 //End of Table
 echo '</table>';
 
 echo "$result";
 ?>[/code]

@anothenauthorJan 30.2006 — #Anyways Try this:

[code=php]<?php
 // open the connection
 $conn = mysql_connect("localhost", "xxxx" , "xxxx");
 
 // pick the database to use
 mysql_select_db("Big_File",$conn);
 
 //Start of Table
 echo '<table border="1" width="50%"><tr>';
 echo '<td align="center"><b>ID</b></td>';
 echo '<td align="center"><b>Name</b></td>';
 echo '<td align="center"><b>Address</b></td>';
 echo '<td align="center"><b>Phone</b></td></tr>';
 
 // create the SQL statement
 $sql = "SELECT * FROM address_book";
 
 // execute the SQL statement
 $result = mysql_query($sql, $conn) or die(mysql_error());
 
 
 //go through each row in the result set and display data
 while ($newArray = mysql_fetch_array($result)) {
 // give a name to the fields
 // index, name, address, phone
 // make $n the index number of arrays holding name address and phone
 
 $index = $newArray['index'];
 $name = $newArray['name'];
 $address = $newArray['address'];
 $phone = $newArray['phone'];
 
 //Start Array Fields within the table
 
 echo '<tr>';
 echo '<td align="center"><b>$index</b></td>';
 echo '<td align="center"><b>$name</b></td>';
 echo '<td align="center"><b>$address</b></td>';
 echo '<td align="center"><b>$phone</b></td></tr>';
 };
 
 //End of Table
 echo '</table>';
 
 echo "$result";
 ?>[/code]

[/QUOTE]
Thanks Aaron, I've almost got it. At least now I can display a table.

Okay, I understand now to place the table command inside the echo command within ' ' .

I also see that it should work, but now it is only displaying the variable name:

[URL=http://www.epourania.com/SAMSphp/16/aTable.php]test display address_book[/URL]

Is there a certain syntax for the quotes that I need to be aware of?

I notice you used the single quote sometimes and double elsewhere.

@LiL_aaronJan 30.2006 — #Oks, maybe the single or double quotes are conlicting

use this

[code=php]<?php 
 // open the connection 
 $conn = mysql_connect("localhost", "xxxx" , "xxxx"); 
 
 // pick the database to use 
 mysql_select_db("Big_File",$conn); 
 
 //Start of Table 
 echo "<table border=1 width=50%><tr>"; 
 echo "<td align=center><b>ID</b></td>"; 
 echo "<td align=center><b>Name</b></td>"; 
 echo "<td align=center><b>Address</b></td>"; 
 echo "<td align=center><b>Phone</b></td></tr>"; 
 
 // create the SQL statement 
 $sql = "SELECT * FROM address_book"; 
 
 // execute the SQL statement 
 $result = mysql_query($sql, $conn) or die(mysql_error()); 
 
 
 //go through each row in the result set and display data 
 while ($newArray = mysql_fetch_array($result)) { 
 // give a name to the fields 
 // index, name, address, phone 
 // make $n the index number of arrays holding name address and phone 
 
 $index = $newArray['index']; 
 $name = $newArray['name']; 
 $address = $newArray['address']; 
 $phone = $newArray['phone']; 
 
 //Start Array Fields within the table 
 
 echo "<tr>"; 
 echo "<td align=center><b>$index</b></td>"; 
 echo "<td align=center><b>$name</b></td>"; 
 echo "<td align=center><b>$address</b></td>"; 
 echo "<td align=center><b>$phone</b></td></tr>"; 
 }; 
 
 //End of Table 
 echo "</table>"; 
 
 //echo "$result"; 
 ?> [/code]

@LiL_aaronJan 30.2006 — #If that one dont work try this one ?

[code=php]<?php 
 // open the connection 
 $conn = mysql_connect("localhost", "xxxx" , "xxxx"); 
 
 // pick the database to use 
 mysql_select_db("Big_File",$conn); 
 
 //Start of Table 
 echo "<table border=1 width=50%><tr>"; 
 echo "<td align=center><b>ID</b></td>"; 
 echo "<td align=center><b>Name</b></td>"; 
 echo "<td align=center><b>Address</b></td>"; 
 echo "<td align=center><b>Phone</b></td></tr>"; 
 
 // create the SQL statement 
 $sql = "SELECT * FROM address_book"; 
 
 // execute the SQL statement 
 $result = mysql_query($sql, $conn) or die(mysql_error()); 
 
 
 //go through each row in the result set and display data 
 while ($newArray = mysql_fetch_array($result)) { 
 // give a name to the fields 
 // index, name, address, phone 
 // make $n the index number of arrays holding name address and phone 
 
 $index = intval($newArray['index']); 
 $name = stripslashes($newArray['name']); 
 $address = stripslashes($newArray['address']); 
 $phone = stripslashes($newArray['phone']); 
 
 //Start Array Fields within the table 
 
 echo "<tr>"; 
 echo "<td align=center><b>$index</b></td>"; 
 echo "<td align=center><b>$name</b></td>"; 
 echo "<td align=center><b>$address</b></td>"; 
 echo "<td align=center><b>$phone</b></td></tr>"; 
 }; 
 
 //End of Table 
 echo "</table>"; 
 
 //echo "$result"; 
 ?> [/code]

@anothenauthorJan 30.2006 — #WEEEEE!

Yaay, that did it, thanks Aaron!

*dances around*

[URL=http://www.epourania.com/SAMSphp/16/btable.php]btable display[/URL]

@LiL_aaronJan 30.2006 — #Welcome mate... was it the last one?

normally i would just do it one my server... but i was doing it notepad.. and just thinking about it ?

so i had no way of testing it ?

Anyways glad it works

and echo "$result";

at the end... you dont need that thats why i put // before it ?

@NogDogJan 30.2006 — #You might find the classes defined here useful: http://www.charles-reace.com/PHP/dbcode.php

@anothenauthorJan 30.2006 — #Welcome mate... was it the last one?

normally i would just do it one my server... but i was doing it notepad.. and just thinking about it ?

so i had no way of testing it ?

Anyways glad it works

and echo "$result";

at the end... you dont need that thats why i put // before it ?[/QUOTE]

Yes it was the last one with the "" quotes.

As far as the echo "$result"; , I was only trying to figure out its value during the "while ()[];" command. I still don't fully understand it. I see that it is needed, but as long as I'm getting the code working, I am satified. I've been working this for week and have just about pulled my hair out. I know there is a version conflict with my server because some of the code that was provided in the book will not run on it.

SAMS provided a disc with all of the php code. The one that I really need, the "select.php" does not work properly. I would post the code here, but I think it is too large. Maybe I'll give it a try.

Anyway, here are the links to the pages:

[URL=http://www.epourania.com/SAMSphp/18/mymenu.php]My Address Book[/URL]

It will add an entry, it will delete an entry, but it will not display an entry. This is why I am taking the time to learn the "select" command and displaying results.

@anothenauthorJan 30.2006 — #You might find the classes defined here useful: http://www.charles-reace.com/PHP/dbcode.php[/QUOTE]
Thanks Nog.

Okay classes, that is what I wanted to understand, that was quite helpful, thanks.

@anothenauthorJan 30.2006 — #This is the file "selentry.php" that will not work.

I might be able to figure it out now that I have a little idea of what classes are. I've searched around to try to find php and mysql reserved variables.

I am guessing that this code might have a version conflict. My server runs php 4.4.2 and the mysql 4.0.26 If this is the case, I will be so lost in trying to find the error.

[code=php]
  <?php
 //connect to database
 $conn = mysql_connect("localhost", "xxxxx" , "xxxx") or die(mysql_error());
 mysql_select_db("Big_File",$conn)  or die(mysql_error());
 
 if ($_POST[op] != "delete")  {
 //haven't seen the form, so show it
 $display_block = "<h1>Select an Entry</h1>";
 
   //get parts of records
   $get_list = "select id, concat_ws(', ', l_name, f_name) as display_name  from master_name order by l_name, f_name";
   $get_list_res = mysql_query($get_list) or die(mysql_error());
 
   if (mysql_num_rows($get_list_res) < 1) {
   //no records
   $display_block .= "<p><em>Sorry, no records to select!</em></p>";
 
   } else {
   //has records, so get results and print in a form
   $display_block .= "
   <form method="post" action="$_SERVER[PHP_SELF]">
   <P><strong>Select a Record to View:</strong><br>
   <select name="sel_id">
   <option value="">-- Select One --</option>";
 
   while ($recs = mysql_fetch_array($get_list_res)) {
   $id = $recs['id'];
   $display_name = stripslashes($recs['display_name']);
 
   $display_block .= "<option value="$id">
   $display_name</option>";
   }
 
   $display_block .= "
   </select>
   <input type="hidden" name="op" value="view">
   <p><input type="submit" name="submit"  value="View Selected Entry"></p>
   </FORM>";
   }
 
 } else if ($_POST[op] == "view") {
 
  //check for required fields
   if ($_POST[sel_id] == "")  {
   header("Location: selentry.php");
   exit;
  }
 
  //get master_info
  $get_master = "select concat_ws(' ', f_name, l_name) as display_name from master_name where id = $_POST[sel_id]";
  $get_master_res = mysql_query($get_master);
  $display_name = stripslashes(mysql_result($get_master_res, 0,'display_name'));
 
 $display_block = "<h1>Showing Record for $display_name</h1>";
 //get all addresses
 $get_addresses = "select address, city, state, zipcode, type from address where master_id = $_POST[sel_id]";
 $get_addresses_res = mysql_query($get_addresses);
 
 if (mysql_num_rows($get_addresses_res) > 0) {
 $display_block .= "<P><strong>Addresses:</strong><br>
 <ul>";
 
   while ($add_info = mysql_fetch_array($get_addresses_res)) {
   $address = $add_info[address];
   $city = $add_info[city];
   $state = $add_info[state];
   $zipcode = $add_info[zipcode];
   $address_type = $add_info[type];
 
   $display_block .= "<li>$address $city $state $zipcode ($address_type)";
   }
 
   $display_block .= "</ul>";
 }
 
 //get all tel
 $get_tel = "select tel_number, type from telephone where  master_id = $_POST[sel_id]";
 $get_tel_res = mysql_query($get_tel);
 
 if (mysql_num_rows($get_tel_res) > 0) {
 $display_block .= "<P><strong>Telephone:</strong><br>
 <ul>";
 
   while ($tel_info = mysql_fetch_array($get_tel_res)) {
   $tel_number = $tel_info[tel_number];
   $tel_type = $tel_info[type];
 
   $display_block .= "<li>$tel_number ($tel_type)";
   }
 
   $display_block .= "</ul>";
 }
 
 //get all fax
 $get_fax = "select fax_number, type from fax where  master_id = $_POST[sel_id]";
 $get_fax_res = mysql_query($get_fax);
 
 if (mysql_num_rows($get_fax_res) > 0) {
 $display_block .= "<P><strong>Fax:</strong><br>
 <ul>";
 while ($fax_info = mysql_fetch_array($get_fax_res)) {
 $fax_number = $fax_info[fax_number];
 $fax_type = $fax_info[type];
 
   $display_block .= "<li>$fax_number ($fax_type)";
   }
 
   $display_block .= "</ul>";
 }
 
 //get all email
 $get_email = "select email, type from email where  master_id = $_POST[sel_id]";
 $get_email_res = mysql_query($get_email);
 
 if (mysql_num_rows($get_email_res) > 0) {
 $display_block .= "<P><strong>Email:</strong><br>
 <ul>";
 
   while ($email_info = mysql_fetch_array($get_email_res)) {
   $email = $email_info[email];
   $email_type = $email_info[type];
 
   $display_block .= "<li>$email ($email_type)";
   }
 
   $display_block .= "</ul>";
 }
 
 //get personal note
 $get_notes = "select note from personal_notes where  master_id = $_POST[sel_id]";
 $get_notes_res = mysql_query($get_notes);
 
 if (mysql_num_rows($get_notes_res) == 1) {
 $note = nl2br(stripslashes(mysql_result($get_notes_res,0,'note')));
 
   $display_block .= "<P><strong>Personal Notes:</strong><br>$note";
 }
 
 $display_block .= "<br><br><P align=center><a href="$_SERVER[PHP_SELF]">select another</a></p>";
 }
 ?>
 <HTML>
 <HEAD>
 <TITLE>My Records</TITLE>
 </HEAD>
 <BODY>
 <? print $display_block; ?>
 </BODY>
 </HTML>
 
 [/code]

@NogDogJan 30.2006 — #I don't have time to read through that line by line, but it appears at first glance to be pretty well constructed with some good defensive coding in there. If there's a specific part of it that's not working, let us know....

Also in #PHP _↴

php Error Drop Down List - Selection Change Help with "automatic forms" or something...

Success!

Help @anothen spread the word by sharing this article on Twitter...

Tweet This

php mysql, How do I select from a table and place into an array?

12 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

php mysql, How do I select from a table and place into an array?

12 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

12 Comments(s) _↴

Also in #PHP _↴