Simple Database question

Davidc316

Ok, let's imagine I have a simple table stored in a MySql database.

Let's imagine the table (stored on MySql) contains 5 rows and 4 columns.

Now all I want to do is to create a webpage which retrieves the information from my database and displays the data it in a nice neat little table.

Does anyone know how to do that?

Thanks!

Xoid

well... so you basically want someone to write you the whole thing?

have you done any research, such as mysql_connect? mysql_select_db? mysql_query? mysql_fetch_array perhaps?

HTH.

Davidc316

I was merely looking for a few lines of code which takes information from a database table and puts displays that info on a table on a html page.

Getting someone "to write the whole thing" makes it sound as if I'm asking someone to give me their right arm.

I know how to create and select the database. I know how to select the table. I know how to select everything from the table (select * from table). I know how to print out all the information from the database. But I do not know how to take that information and draw it onto a table.

Of the terms you mentioned, the only one I'm not familiar with is mysql_fetch_array. What exactly does that do?

leatherback

Hi,

mysql_fetch_array() is the magic little function you have been waiting for your whole life, so now you can get the info from a databasequery result, and "do" 'something' with it.
But I must agree with Xoid a bit.. Have a look at the manual...

In general:

$result = mysql_query(' select * from database  ');
  while ($row =@ mysql_fetch_array($result));
  {
  echo $row[somecolum];
  }

//or, what I prefer:

$result = mysql_query(' select * from database  ');
  while ($row =@ mysql_fetch_array($result));
  {
  $output .= $row[somecolum];
  $output .= "<b>SomeHtmlCodingHere</b>";
  }

echo $output

Play around, jump up and down, and if you can't get it to work..
Start a new thread and ask a specific question.

kevinsequeira

well, why dont u post what u have written till now and we could help u format it

reg
kevin

Davidc316

Ok, thanks for that...

So far I have made a practice database with a table called "grocery_inventory". It's a tiny database and the table is very small with five columns called "id, name, description, price and quantity". There are only a few items on this table such as "apples" "bananas" and so on.

Anyway... like I say... so far I know how to call up the items on this database and display them as text, but I don't know how to put them into a nice neat looking table.

Here is the code I have so far:

<?php
// open the connection
$conn = mysql_connect ("localhost", "root", "");

// pick the database to use
mysql_select_db ("dcdatabase",$conn);

// create the sql command
$sql = "Select * from grocery_inventory";

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

// go through each row and display data
while ($shit = mysql_fetch_array($result)) {
// give a name to the fields
$id =$shit['id'];
$name = $shit['item_name'];
$desc = $shit['item_desc'];
$price = $shit ['item_price'];
$qty = $shit ['curr_qty'];

  //print the results on screen
  echo "The ID is $id and the text is $name <br>";
  echo "The description is $desc and the price is $price <br>";
  echo "The current quatity for this item is $qty<br>";
  print "<br>";
  print "<br>";

}
?>

When I use the above code I get a plain html page which says the following:

The ID is 1 and the text is Apples
The description is Medium-sized Granny Smith and the price is 0.25
The current quatity for this item is 1000

The ID is 2 and the text is Buches of Grapes
The description is Seedless grapes and the price is 0.99
The current quatity for this item is 500

The ID is 3 and the text is Bottled Water (6 pack)
The description is 500ml spring water. and the price is 0.99
The current quatity for this item is 250

The ID is 4 and the text is Pineapple
The description is A big one and the price is 1.25
The current quatity for this item is 500

The ID is 5 and the text is Oranges
The description is Lots of seeds and sour and the price is 0.52
The current quatity for this item is 750

The ID is 6 and the text is Pint of Milk
The description is nearly out of date and the price is 0.85
The current quatity for this item is 80

...so as you can see, I've got a long way to go before that turns into a table!

David

PS- My database is called "dcdatabase" and I'm just using the server on my computer at home (i.e. localhost)

DeltaRho2K

<?php 
// open the connection 
$conn = mysql_connect ("localhost", "root", ""); 

// pick the database to use 
mysql_select_db ("dcdatabase",$conn); 

// create the sql command 
$sql = "Select * from grocery_inventory"; 

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

echo "<table><tr>
          <th>ID</th>
          <th>Name</th>
          <th>Description</th>
          <th>Price</th>
          <th>Qty</th></tr>";


// go through each row and display data 
while ($shit = mysql_fetch_array($result)) { 
// give a name to the fields 
$id =$shit['id']; 
$name = $shit['item_name']; 
$desc = $shit['item_desc']; 
$price = $shit ['item_price']; 
$qty = $shit ['curr_qty']; 

//print the results on screen 
echo "<tr><td>" .$id. "</td>";
echo "<td>" .$name. "</td>";
echo "<td>" .$desc. "</td>";
echo "<td>" .$price. "</td>";
echo "<td>" .$qty. "</td></tr>";
} 

echo "</table>";
?>

Davidc316

Thanks VERY much for that piece of coding!

I can say for a fact that some of the things you did in that code are not covered in the book I'm working my way through!

I've still got a couple of small issues that I'm trying to figure out- i.e., how to insert table borders round the cells and how to align the table headings to the left hand side.

When I do a print "<table border =1>"; it comes out as a bit of a mess.

But DON'T WORRY. I'm not complaining and I don't want to push my luck.

I'll fiddle around with your code for a few more hours and then I'll come back and mark this thread as resolved.

Thanks!!!

Norman_Graham

Hi David

Me again - the problem with your question is that you're basically asking how to write HTML. This doesn't go down too well in these parts since you're expected to know a bit of HTML.

However, the thing with getting stuff out of a database and sticking it in a table is that one assumes there's going to be more than one result. So you want to use mysql_fetch_array() and then make sure that the correct table tags are being inserted between the contents of the array (using the concatenate syntax, otherwise known as spaces and full-stops). The tags themselves are just common or garden HTML, though, so you shouldn't need a book to tell you what to do with them.

HTH

Norm 😃

DeltaRho2K

Hey David, I agree with Norman, but I will at least take the time to answer your question about putting borders in there...

First, I don't recommend using the border attribute (<table border=1>) simply due to the fact that the default table border is hideous looking, and you can't specify different border widths and colors in a cross browser environment.

Instead, I recommend using CSS (Cascading Style Sheets)

Try rewriting the table tag as this:

<table style=\"border: 1px; border-style: solid; border-color: #CCCCCC\">

This will give your table a gray solid 1 px border. (for example)

You can also specify attributes in the <td> tags as well the same way.

I recommend you take some time to visit http://www.w3c.org and read all about how to use style sheets, because it is amazing what you can do with it. While there, read up a bit about tables, and general HTML. It sounds like you are trying to run before you learned to walk, and it makes it tough to learn. Besides, I hate to see people get bruised up (with their posts) like you have here.

Good luck with it...

~DR

One more thing, you can't align contents within <th></th> tags to the left. If you want those contents left aligned, change the tags to <td></td> tags.

Davidc316

Thank for that valuable post Delta

DeltaRho2K

No problem David. You should be getting an email from me with some helpfule stuff in it. Let me know if it helps.