Turning MySQL data into a PHP table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Turning MySQL data into a PHP table

Post by synical21 »

I know i need to make a query like

SELECT id,LEFT(title,20) FROM "tablename"

then do that for each column in the database i want on the table.

now all i need to know is how to actually echo back a table to put my queries on.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Turning MySQL data into a PHP table

Post by requinix »

synical21 wrote:I know i need to make a query like

SELECT id,LEFT(title,20) FROM "tablename"

then do that for each column in the database i want on the table.
Uhh... what?

Code: Select all

SELECT * FROM `tablename`
That'll get you everything in the table. You don't need multiple queries for multiple columns.

Anyways, an example:

Code: Select all

$result = mysql_query("SELECT * FROM `tablename`");
 
echo "<table>";
echo "<tr><th>ID</th><th>Title</th></tr>";
if (mysql_num_rows($result) == 0) {
    echo "<tr><td colspan='2'>No results</td></tr>";
}
while ($line = mysql_fetch_array($result)) {
    echo "<tr>";
    echo "<td>", htmlentities($line["id"]), "</td>";
    echo "<td>", htmlentities($line["title"]), "</td>";
    echo "</tr>";
}
echo "</table>";
Exactly how you print the table depends on what you're printing and why.
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: Turning MySQL data into a PHP table

Post by synical21 »

Thanks for the reply i will try your method now :D
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: Turning MySQL data into a PHP table

Post by synical21 »

Ok this is the code im using now and it does the job except i cant add more queries, need just a little help on how to add another column.

This is the query:

Code: Select all

 
$sql = 'SELECT id,LEFT(title,20) as short_title FROM fulldata'; 
$res = mysql_query($sql);
if(!$res) die(mysql_error());
This is the table:

Code: Select all

echo '<table>';
echo '<tr><th>id#</th><th>Title</th></tr>';
while($row = mysql_fetch_assoc($res)) {
  echo '<tr>'.
       '<td>'.$row['id'].'</td>'.
       '<td><a href="fulldata.php?ID='.$row['id'].'">'.$row['short_title'].'</a></td>'.
       '</tr>';
}
Now this works exactly how i want it to it allows me to link the title to a new php page. Only problem now is when i try to add the new column "min" it just overlaps the title and goes wrong. i just want it to go like:
ID---Title----------------Min
1.....Title info here.....3

Hope that makes it a little clearer


Thanks for viewing.
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: Turning MySQL data into a PHP table

Post by synical21 »

Anyone :cry:
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Turning MySQL data into a PHP table

Post by jackpf »

What do you mean "add more queries", and overlaps the title? And what's your new code?

Might be just me, but your post seems a little unclear...
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: Turning MySQL data into a PHP table

Post by synical21 »

Ill just show the script in one piece

Code: Select all

# connect to the database
mysql_connect('localhost','*****','****');
mysql_select_db('Mini');
 
# query
$sql = 'SELECT id,LEFT(title,20) as short_title FROM fulldata'; 
$res = mysql_query($sql);
if(!$res) die(mysql_error());
 
#HTML table
echo '<table>';
echo '<tr><th>id#</th><th>Title</th></tr>';
while($row = mysql_fetch_assoc($res)) {
  echo '<tr>'.
       '<td>'.$row['id'].'</td>'.
       '<td><a href="fulldata.php?ID='.$row['id'].'">'.$row['short_title'].'</a></td>'.
       '</tr>';
}
echo '</table>';
So far the script makes a table with a title column


To make it less confusing i will just sum up, i basically need a table with title,minutes and costs as columns, but i am finding it hard to add more columns as when ever i try to add the minutes next to the title column the title column just disappears even though i have not edited the title part.

So i just need a way to add multiple columns to my table as i can only manage one column.

That might clear it up i dunno
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Turning MySQL data into a PHP table

Post by jackpf »

Code: Select all

echo '<table>';
    echo '<tr><th>id#</th><th>Title</th><th>Third column</tr>';
    while($row = mysql_fetch_assoc($res)) {
      echo '<tr>'.
           '<td>'.$row['id'].'</td>'.
           '<td><a href="fulldata.php?ID='.$row['id'].'">'.$row['short_title'].'</a></td><td>Third column</td>'.
           '</tr>';
   }
Like that??
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: Turning MySQL data into a PHP table

Post by synical21 »

Ahhhh now its starting to look more like a table. I think i can work the rest from here dam that was to easy, i need a break from php i think.... trying to learn to much in my first 3 days of learning it for the first time :(

One last question so i dont have to bug you again. The other two columns are not included in the query yet, i presume i have to edit the query to include 'min' and 'cost'.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Turning MySQL data into a PHP table

Post by jackpf »

Tbh, I think you need to learn some HTML first :P

In regard to your question - read tasairis's post again.
synical21
Forum Contributor
Posts: 150
Joined: Tue Jul 28, 2009 8:44 am
Location: London UK

Re: Turning MySQL data into a PHP table

Post by synical21 »

Table done thanks, Yeah i need to learn html urgently i was hopeing i would pick it up as i tried to learn php but thats not the case. Never knew scripting would be so hard.

Ah well thanks again.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Turning MySQL data into a PHP table

Post by jackpf »

Yeah, that generally is the case...I think html is a language you just kind of pick up, not necessarily learn.

However...it's always good to run through a tutorial if you're just starting out. There's an awesome tutorial here that I used when I started out learning HTML. It'll probably take you like an hour...and you'll pretty much "know" html.
Post Reply