php/mysql basic scripting (basic for you, confusing for me)

Hey all, I need some help. After looking through a couple hundred articles on the web, as well as the php and mysql sites, I am rather confused. What I need is simple, I need a script that does two things,

writes four fields to a table from a form, call them fieldA, fieldB, fieldC, fieldD

queries and returns the result to four variables that I can put into a page where I need them.

The following is what I got from php.net, it works fine (others I tried gave me unknown T_VARIABLE on line... errors).

The problem is, I need the results returned in a way that I can place them into html where they are needed, not just into a table.

PHP Code:

<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
   or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');

// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>

I really appreciate the help, this is driving me nuts.

Originally Posted by Trip59

PHP Code:

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n"; 


All you have to do is customize the while loop to output the data in whatever format you want. The while loop, goes through each row of the result set and in your example echoes it as part of a table. That's the only statement that you need to modify. Try playing around with it to get a better idea of how it works. For example try changing the <td> to <td align="left"> or <td align="right"> or change the background color. This way you get a better idea of what the while loop is doing.

Also, I can see that you are using the mysql_fetch_array function.
These functions return an array but it is associative meaning the elements can be accessed by column name. For example: $row['name']. I personally prefer this method because it makes the code a bit easier to read and I don't have to remember the order of columns. The numerically indexed arrays are handy for dumping table structures though because you can loop off the array size and don't have to change your code if you change your database schema. Hope this makes sense. I'll try to post an example a bit later for you.

I have been customizing other scripts, but I couldn't seem to get the loop to work on this one. An example would be much appreciated, whenever I try to use $row['name'] I wind up getting a T_VARIABLE error, perhaps an example would help me understand what I did wrong (I deleted the non-working ones or I'd post one)

[roleli]

PHP Code:

$line  = mysql_fetch_array($result, MYSQL_ASSOC) 


since the variable name in this case is $line you have to use

$line['name'] etc

since you have an associative array, you should use

PHP Code:

foreach($line as $col_name => $col_value) 


gives you in $col_name, the name of the column and in $col_value the value it held.

Is there any specific use you want here, or just something in general?

Basically what I'm looking for is a way to wind up with

PHP Code:

echo "<td>$row["id"]</td><td>$row["product"]</td><td>$row["price"]</td>"; 


I need to be able to write a loop to display what I've pulled.

The other part I haven't started on yet is how to take a form and write the fields to the table.