MySQL / PHP help needed

P

PearlJam

Expert Member
Joined
Jan 22, 2006
Messages
2,181
Reaction score
15
Location
Johannesburg
Hi, i am just starting to do the above and have managed so far to connect to the db and do a show tables command. How do i change the returned values into links to enable me to create edit / add / create pages for the db?

Here is my code if it helps:

PHP: 
<?php $result = mysql_query( "SHOW TABLES" ) 
				or die("SELECT Error: ".mysql_error()); 
				$num_rows = mysql_num_rows($result); 
				print "<h3>Tables</h3><P>"; 
				print "<table width=200 >\n"; 
				while ($get_info = mysql_fetch_row($result)){ 
				print "<tr>\n"; 
				foreach ($get_info as $field) 
				print "\t<td><font face=verdana size=3/>$field</font></td>\n"; 
				print "</tr>\n"; 
				} 
				print "</table>\n"; 

			?>
 
I suggest looking at more examples of interacting with MySql from PHP.

Here are the basics to get you started:

Create a Table called users
PHP: 
<?
$qry = mysql_query("CREATE TABLE users (userID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,name VARCHAR(35),surname VARCHAR(35),email VARCHAR(70),
pass VARCHAR(12))");
?>

Add a new record to the users table:
PHP: 
<?
$qry = mysql_query("INSERT INTO users (name,surname,email,pass) VALUES ('Jim','Franklin','[email protected]','jimspassword')");
?>

Retrieve a record from the table (not a very good way of doing it):
PHP: 
$qry = mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_row($qry)){
print "<a href=\"?userid=$row[0]\">$row[1] $row[2]</a> Email Address: <a href=\"mailto:$row[3]\">$row[3]</a>";
}

Rather than the previous method, select specific fields from your tables, that way you know that if the table changes but the field names stay the same, then you don't need to edit all your queries:
PHP: 
$qry = mysql_query("SELECT userID,name,surname,email FROM users");
while ($row = mysql_fetch_row($qry)){
print "<a href=\"?userid=$row[0]\">$row[1] $row[2]</a> Email Address: <a href=\"mailto:$row[3]\">$row[3]</a>";
}

To select rows with special criteria:
PHP: 
/* SELECT ALL RECORDS WHOSE SURNAME STARTS WITH F */
$qry = mysql_query("SELECT userID,name,surname,email FROM users WHERE surname LIKE 'F%'");
while ($row = mysql_fetch_row($qry)){
print "<a href=\"?userid=$row[0]\">$row[1] $row[2]</a> Email Address: <a href=\"mailto:$row[3]\">$row[3]</a>";
}

/* SELECT ALL RECORDS WHOSE NAME STARTS WITH J */
$qry = mysql_query("SELECT userID,name,surname,email FROM users WHERE name LIKE 'J%'");
while ($row = mysql_fetch_row($qry)){
print "<a href=\"?userid=$row[0]\">$row[1] $row[2]</a> Email Address: <a href=\"mailto:$row[3]\">$row[3]</a>";
}

/* SELECT ALL RECORDS WHOSE NAME DOES NOT START WITH J */
$qry = mysql_query("SELECT userID,name,surname,email FROM users WHERE name NOT LIKE 'J%'");
while ($row = mysql_fetch_row($qry)){
print "<a href=\"?userid=$row[0]\">$row[1] $row[2]</a> Email Address: <a href=\"mailto:$row[3]\">$row[3]</a>";
}

/* SELECT RECORDS WHERE FIELDS ARE EXACT MATCH TO CRITERIA */
$qry = mysql_query("SELECT userID,name,surname,email FROM users WHERE userID = '1'");
while ($row = mysql_fetch_row($qry)){
print "<a href=\"?userid=$row[0]\">$row[1] $row[2]</a> Email Address: <a href=\"mailto:$row[3]\">$row[3]</a>";
}

To delete a record from a table:
PHP: 
$qry = mysql_query("DELETE FROM users WHERE userID = '1'");
 
OK, but what i want to do is to have all tables listed with links...if you click on the link it takes you to edit page or create, online

Even if i can just get the correct function names, etc. then i can google it myself...i just don't know exactly what to search for
 
Do some MySQL/PHP tutorials first - you'll get a much better picture of how everything fits together.

I suggest this tutorial : http://www.webmonkey.com/programming/php/tutorials/tutorial4.html

It will take you from how to install PHP and MySQL right up to how to insert data into MySQL using PHP and forms which is basically what you want to do with your edit and create idea.
 
Can someone please help me to create a short php script to display a Table in my database. The Table has 7 Columns & 32 Rows.

I'm using a mySQL Database & using phpMyAdmin to access it.
This is the code I got mrom phpMyAdmin:
$sql = 'SELECT * FROM `Template` LIMIT 0, 30 ';

Can someone tell me how to connect to the database (I have all the necessary info i.e Host, username etc)
 
mh348 said:
Can someone please help me to create a short php script to display a Table in my database. The Table has 7 Columns & 32 Rows.

I'm using a mySQL Database & using phpMyAdmin to access it.
This is the code I got mrom phpMyAdmin:
$sql = 'SELECT * FROM `Template` LIMIT 0, 30 ';

Can someone tell me how to connect to the database (I have all the necessary info i.e Host, username etc)
Click to expand...

This is how i did it:

Connection settings (This is a seperate file called "constants.php" which you will notice in the next code

PHP: 
<?php

// Database Constants
define("DB_SERVER", "localhost");
define("DB_USER", "your user name");
define("DB_PASS", "your password");
define("DB_NAME", "your db name");

?>

This is the actual page which connects to the db:

PHP: 
<?php
require("constants.php");

// 1. Create a database connection
$connection = mysql_connect(DB_SERVER,DB_USER,DB_PASS);
if (!$connection) {
	die("Database connection failed: " . mysql_error());
}

// 2. Select a database to use
$db_select = mysql_select_db(DB_NAME,$connection);
if (!$db_select) {
	die("Databse selection failed: " . mysql_error());
}
?>

This is a query to show the registered users from the db in a column layout:


PHP: 
<?php

// create and execute query
$query = 'SELECT * FROM Users';
$result = mysql_query($query)
or die ('Error in query: $query. ' . mysql_error());
// check if records were returned
if (mysql_num_rows($result) > 0)
{
// print HTML table
echo '<table width=100% cellpadding=10 cellspacing=0 border=1>';
echo
'<tr><td><b>ID</b></td><td><b>User Name</b></td><td><b>Password</b></td><td><b>First Name</b></td><td><b>Last Name</b></td><td><b>Email</b></td></tr>';
// iterate over record set
// print each field
while($row = mysql_fetch_row($result))
{
echo '<tr>';
echo '<td>' . $row[0] . '</td>';
echo '<td>' . $row[1] . '</td>';
echo '<td>' . $row[2] . '</td>';
echo '<td>' . $row[3] . '</td>';
echo '<td>' . $row[4] . '</td>';
echo '<td>' . $row[5] . '</td>';
echo '</tr>';
}
echo '</table>';
}
else
{
// print error message
echo 'No rows found!';
}

// once processing is complete
// free result set
mysql_free_result($result);


?>

Other than this, maybe look at the same tutorial as mentioned by Paul_S
 
Use the online manuals. It's very useful because of the comments at the bottom of each page, including sample code.

The MySQL part of the manual.
 
Last edited:
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X