Log in

View Full Version : Resolved display mysql entries on site?



liamallan
02-15-2010, 11:10 PM
i finally got my head round inserting data in mysql through an html form, but now i want to display all of the table entries on a page on my site.

can anyone let me know how to do it?
any help will be appreciated, thanx

Nile
02-15-2010, 11:31 PM
Something like:


$query = mysql_query("SELECT * FROM `table`") or die(mysql_error());
while($row = mysql_fetch_assoc($query)){
echo $row['field'];
}

liamallan
02-15-2010, 11:54 PM
yes, something like that mate. but i want to add entries in a table.
the fields on my db are: name and profile

auriaks
02-16-2010, 12:02 AM
$name = 'thomas';
$profile = 'profile';

$query = mysql_query("INSERT INTO table(name, profile) VALUES('$name', '$profile')") or die(mysql_error());

liamallan
02-16-2010, 12:22 AM
im new to php, so you might need to explain a little.

my situation is: created form on shiftysplayground.co.cc/addfriends.php to post data (name, profile) into database.
the plan is to have a table of users below the form on shiftysplayground.co.cc/addfriends.php, taken from database.
will i need to add the php code to the page addfriends.php or the file supplying the action?

fileserverdirect
02-16-2010, 12:48 AM
assuming the form on addfriends.php has an action of process and a method of POST, you would combine the code\ideas from the above posts:


<?php
//process.php
$host="yourhost";
$username="yourusername";
$password="yourpass";
$db_name="yourDB";
$tbl_name="yourTABLE";

mysql_connect("$host", "$username", "$password")or die("cannot connect to server");
mysql_select_db("$db_name")or die("cannot select db");

$name = $_POST['name'];
$profile = $_POST['profile'];

$query = mysql_query("INSERT INTO $tbl_name(name, profile) VALUES('$name', '$profile')") or die(mysql_error());
header('location: addfriends.php');
?>

and your add friends page that dispalys a list:


<?php
//process.php
$host="yourhost";
$username="yourusername";
$password="yourpass";
$db_name="yourDB";
$tbl_name="yourTABLE";

mysql_connect("$host", "$username", "$password")or die("cannot connect to server");
mysql_select_db("$db_name")or die("cannot select db");
$query = mysql_query("SELECT * FROM `table`") or die(mysql_error());
?>
<!-- Here's where your form goes, then place this beneath it or wherever you want the list.... -->
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="2"><strong>Entries</strong> </td>
</tr>

<tr>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>Profile</strong></td>

</tr>
<?php
while($rows=mysql_fetch_array($query)){
?>
<tr>
<td><? echo $rows['name']; ?></td>
<td><? echo $rows['profile']; ?></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>

****Untested******

liamallan
02-16-2010, 01:26 AM
thank you mate, it seems to be inputting the data into the table very well.

i have another question though,

new entries seem to be joining at the bottom of the table, how could i make it so they join at the top?

also, the data entered into the profile field is a url, is there a way i could make the url fields a clickable link that reads 'add friend'?

thanks again for your help

Nile
02-16-2010, 01:39 AM
To make it display descending change:


$query = mysql_query("SELECT * FROM `table`") or die(mysql_error());

To:


$query = mysql_query("SELECT * FROM `table` DESC") or die(mysql_error());


For profile, change:

<td><? echo $rows['profile']; ?></td>

To:


<td><a href="<? echo $rows['profile']; ?>">Add Friend</a></td>

liamallan
02-16-2010, 01:48 AM
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC' at line 1
does that mean it doesnt recognise 'desc'?

Nile
02-16-2010, 01:52 AM
No, it needs to order iteself by something... Do you have an id or date column?

auriaks
02-16-2010, 01:54 AM
you must say:

$query = mysql_query("SELECT * FROM `table` ORDER BY 'date' DESC") or die(mysql_error());

I think you must say ORDER BY

Nile
02-16-2010, 01:55 AM
Yes, order by is a must, but we dont know if he has a date column.

liamallan
02-16-2010, 01:57 AM
no, just name and profile. will i need to add an id or date column?

Nile
02-16-2010, 01:59 AM
Yes. In PHPmyadmin click on the table you're using and then press SQL and put this code in:


ALTER TABLE `table-name` ADD `id` INT NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY ( `id` )

Then for the query:


$query = mysql_query("SELECT * FROM `table` ORDER BY 'id' DESC") or die(mysql_error());

liamallan
02-16-2010, 02:06 AM
thanks guys, working perfect now! top dollar!

Nile
02-16-2010, 02:13 AM
Glad to help you! Your welcome!

It seems your topic is solved... Please set the status to resolved.. To do this:
Go to your first post ->
Edit your first post ->
Click "Go Advanced" ->
Then in the drop down next to the title, select "RESOLVED"