PDA

View Full Version : MySQL & PHP



spider1405
07-26-2006, 11:42 PM
Hi Everyone,

I want to create a MySQL database or access database to display a car dealer stocks on different pages.

Eg: One Page for Used Cars, One for Light Commercials, one for new cars, etc. and stocks should be able to be entered into the dbase using a simple user interface on a php page like an admin page.

If you could help that would be great!

jr_yeo
07-27-2006, 10:57 AM
reading through this (http://www.php-mysql-tutorial.com/) site might help :p

costas
08-18-2006, 10:43 AM
Do you have any knowledge of how you access data from MYSQL using PHP?

jr_yeo
08-18-2006, 01:30 PM
Do you have any knowledge of how you access data from MYSQL using PHP?
just a little :cool:

shachi
08-18-2006, 02:55 PM
jr_yeo: I think costas was asking the OP and not you.:p

jr_yeo
08-18-2006, 06:59 PM
oh.... my mistake :cool:

costas
08-19-2006, 11:30 AM
Well, I think I could help. Give me some details and I see what can I do.

spider1405
08-24-2006, 07:02 PM
Hi,

I have scrapped that first idea. I need to have a database which contains details of Mobile Phone Dealers. I have set up a MySQL database but nothing else.

I need to be able to add new dealers & edit existing dealers in the dbase from a php page.

Dealer details to be saved in dbase:
Delaer Name, Delaer Phone, Dealer Fax, Email, Website (with link), address, phone operators (4 operators listed below), description, delaer logo/image

I need to add a search form to a php page that will search the db and display results on a page.

The search should be done on these items:
County/State: There will be 26 counties
Phone Networks/Operators: There will be 4 operators: Vodafone, O2, 3 Ireland, Meteor

A person using the search should be able to select multiple operators but only one county or an option for All Counties.

Can anybody help me to do this?

costas
08-27-2006, 10:47 AM
Hi Spider1405,

I think I can help, but the answer is quite big so it'll take one day or two.

Regards

spider1405
08-27-2006, 12:07 PM
thanks costas

costas
09-06-2006, 03:44 PM
Spider1405 wrote:


I have scrapped that first idea. I need to have a database which contains details of Mobile Phone Dealers. I have set up a MySQL database but nothing else.

I need to be able to add new dealers & edit existing dealers in the dbase from a php page.

Dealer details to be saved in dbase:
Delaer Name, Delaer Phone, Dealer Fax, Email, Website (with link), address, phone operators (4 operators listed below), description, delaer logo/image

I need to add a search form to a php page that will search the db and display results on a page.

The search should be done on these items:
County/State: There will be 26 counties
Phone Networks/Operators: There will be 4 operators: Vodafone, O2, 3 Ireland, Meteor

A person using the search should be able to select multiple operators but only one county or an option for All Counties.

Well, the first thing you have to do is to create a new table in the database you created called "dealers" or something like that. In that table you'll add 13 fields one for each dealer detail. The name of each field will be as you said: e.g. one would be dealer_name. The only thing to concern is that you need to have four fields for the operators even if the dealer doesn't select them all.
OK, now we need the code about the php files. First we'll need to have a file from where we create the table. Then you'll need a main page called main.php or main.html, where there should something like a menu (or just links) where you can choose either to add a new dealer or to edit or delete an existing one. I think that the code of this page is fairly easy so I won't write here, but if you have any problems just say it and I'll be glad to help. Then we need a file called list.php where all the dealer will be listed and from where you can who do you want to see providing also a link to a file show.php and edit.php. The show.php file is file where you see the details of a dealer. Edit.php is a page, where we can edit the existing dealers. Then, we should have an add.php file, which will provide you a form to add a new dealer. In addition, we should have a delete.php file, from where we can delete an existing dealer.

The code is in the next post as it has more character than the maximum allowed!!:D

costas
09-06-2006, 03:55 PM
Me again, here's some of
The code:

table.php:


<?
$conn = mysql_connect("somehost", "username", "password") or die(mysql_error());
mysql_select_db("database_name", $conn) or die(mysql_error());
$sql = "CREATE TABLE `dealers` (
`dealer_name` VARCHAR( 100 ) NOT NULL ,
`dealer_phone` VARCHAR( 100 ) NOT NULL ,
`dealer_fax` VARCHAR( 100 ) NOT NULL ,
`email` VARCHAR( 100 ) NOT NULL ,
`website` VARCHAR( 100 ) NOT NULL ,
`address` VARCHAR( 100 ) NOT NULL ,
`operator1` VARCHAR( 20 ) NOT NULL ,
`operator2` VARCHAR( 20 ) NOT NULL ,
`operator3` VARCHAR( 20 ) NOT NULL ,
`operator4` VARCHAR( 20 ) NOT NULL ,
`description` VARCHAR( 200 ) NOT NULL ,
`dealer_image` VARCHAR( 100 ) NOT NULL ,
`county` VARCHAR( 100 ) NOT NULL
) ";

$sql_res = mysql_query($sql, $conn) or die(mysql_error());
?>

list.php


<?
function doDB()
{
global $conn;
$conn = mysql_connect("somehost", "username", "password") or die(mysql_error());
mysql_select_db("database_name", $conn) or die(mysql_error());
}

function getdeal()
{
global $sql_res;
$sql = "select dealer_name from dealers";
$sql_res = mysql_query($sql, $conn) or die(mysql_error());
}

doDB();
getdeal();

if (mysql_num_rows($sql_res) >= 1)
{
while($row = mysql_fetch_array($sql_res))
{
$dealer_name = stripslashes($row['dealer_name']);
$msg = "<a href=show.php?name=$dealer_name>$dealer_name</a><br>
<a href=edit.php?name=$dealer_name>EDIT?</a>";
}
}
else $msg = "No Dealers!";
?>
<html>
<head>
<title>Dealers List</title>
</head>
<body>
<center><h2>DEALERS LIST</h2><p>
<? echo $msg; ?>
</center>
</body>
</html>


show.php


<?
$name = $_GET[name];

function doDB()
{
global $conn;
$conn = mysql_connect("somehost", "username", "password") or die(mysql_error());
mysql_select_db("database_name", $conn) or die(mysql_error());
}

function getdeal()
{
global $sql_res;
$sql = "select * from dealers where dealer_name = '$name'";
$sql_res = mysql_query($sql, $conn) or die(mysql_error());
}

doDB();
getdeal();

if (mysql_num_rows($sql_res) == 1)
{
$row = mysql_fetch_array($sql_res);
$dealer_name = stripslashes($row['dealer_name']);
$dealer_phone = stripslashes($row['dealer_phone']);
$dealer_fax = stripslashes($row['dealer_fax']);
$email = stripslashes($row['email']);
$website = stripslashes($row['website']);
$address = stripslashes($row['address']);
$operator1 = stripslashes($row['operator1']);
$operator2 = stripslashes($row['operator2']);
$operator3 = stripslashes($row['operator3']);
$operator4 = stripslashes($row['operator4']);
$description = stripslashes($row['description']);
$dealer_image = stripslashes($row['dealer_image']);
$county = stripslashes($row['county']);

$msg = "<ol><li>Name: $dealer_name</li>
<li>Phone: $dealer_phone</li>
<li>Fax: $dealer_fax</li>
<li>Email: $email</li>
<li>Website:<a href=$website>$website</a></li>
<li>Address: $address</li>
<li>Operators: $operator1 $operator2 $operator3 $operator4</li>
<li>Description: $description</li>
<li>County: $county</li>
<li>Image: <img src=$dealer_image></li>
</ol>";
}
else $msg = "Invalid Dealer Name";

?>
<html>
<head>
<title>Showing Details</title>
</head>
<body>
<center><h2>SHOWING DETAILS FOR $dealer_name</h2><p>
<? echo $msg; ?>
</center>
</body>
</html>


add.php


<?
function doDB()
{
global $conn;
$conn = mysql_connect("somehost", "username", "password") or die(mysql_error());
mysql_select_db("database_name", $conn) or die(mysql_error());
}

doDB();
$op = $_POST[op];

if($op != "ds")
{
$msg = "<form method=post action=\"$_SERVER[PHP_SELF]\">
<p><b>Name:</b> <input type=text name=name><br>
<b>Phone: </b><input type=text name=phone><br>
<b>Fax: </b><input type=text name=fax><br>
<b>Email: </b><input type=text name=email><br>
<b>Website: </b><input type=text name=website><br>
<b>Address: </b><input type=text name=address><br>
<b>Operators: </b><select multiple name=\"operator[]\">
<option value=Vodafone>Vodafone</option>
<option value=O2>O2</option>
<option value=\"3 Ireland\">3 Irealand</option>
<option value=Meteor>Meteor</option>
</select><br>
<b>Description: </b><textarea name=description></textarea><br>
<b>Path to Image: </b><input type=text name=image><br>
<b>County: </b><select name=county>Have your options here!!</select><br>
<input type=hidden name=op value=ds>
<input type=submit value=Add>
</form>";
}
else if($op == "ds")
{
$name = $_POST[name];
$phone = $_POST[phone];
$fax = $_POST[fax];
$email = $_POST[email];
$website = $_POST[website];
$address = $_POST[address];
$operator = $_POST[operator];
$description = $_POST[description];
$image = $_POST[image];
$county = $_POST[county];

if ($name != NULL && $phone != NULL e.t.c.)
{

$sq = "insert into dealers values ("$name", "$phone", "$fax", "$email", "$website", "$address", "$operator[0]", "$operator[1]", "$operator[2]", "$operator[3]", "$description", "$image", "$county")";
$sq_res = mysql_query($sq, $conn) or die(mysql_error());
$msg = "New Dealer has been added in the database";
}
else {
header("Location: add.php");
exit;
}
}
?>
<html>
<head>
<title>ADD NEW DEALER</title>
</head>
<body>
<center><h2>ADD NEW DEALER</h2><p>
<? echo $msg; ?>
</center>
</body>
</html>


edit.php


<?
$name = $_POST[name];

function doDB()
{
global $conn;
$conn = mysql_connect("somehost", "username", "password") or die(mysql_error());
mysql_select_db("database_name", $conn) or die(mysql_error());
}

function getdeal()
{
global $sql_res;
$sql = "select * from dealers where dealer_name = '$name'";
$sql_res = mysql_query($sql, $conn) or die(mysql_error());
}

doDB();
getdeal();

if ($_POST[op] != "ds")
{

if (mysql_num_rows($sql_res) == 1)
{
$row = mysql_fetch_array($sql_res);
$dealer_name = stripslashes($row['dealer_name']);
$dealer_phone = stripslashes($row['dealer_phone']);
$dealer_fax = stripslashes($row['dealer_fax']);
$email = stripslashes($row['email']);
$website = stripslashes($row['website']);
$address = stripslashes($row['address']);
$operator1 = stripslashes($row['operator1']);
$operator2 = stripslashes($row['operator2']);
$operator3 = stripslashes($row['operator3']);
$operator4 = stripslashes($row['operator4']);
$description = stripslashes($row['description']);
$dealer_image = stripslashes($row['dealer_image']);
$county = stripslshes($row['county']);

$msg = "<form method=post action=\"$_SERVER[PHP_SELF]\">
<p><b>Name:</b> <input type=text name=name value=$dealer_name><br>
<b>Phone: </b><input type=text name=phone value=$dealer_phone><br>
<b>Fax: </b><input type=text name=fax value=$dealer_fax><br>
<b>Email: </b><input type=text name=email value=$email><br>
<b>Website: </b><input type=text name=website value=$website><br>
<b>Address: </b><input type=text name=address value=$address><br>
<b>Operators: </b> <input type=text readonly=true value=\"$operator1 $operator2 $operator3 $operator4\"><br>
<b>Choose other:</b><select multiple name=\"operator[]\">
<option value=Vodafone>Vodafone</option>
<option value=O2>O2</option>
<option value=\"3 Ireland\">3 Irealand</option>
<option value=Meteor>Meteor</option>
</select><br>
<b>Description: </b><textarea name=description value=$description>$description</textarea><br>
<b>Path to Image: </b><input type=text name=image value=$image><br>
<b>County: </b><input type=text name=county value=$county readonly=true><br>
<b>Choose Other: <select name=county>Have your options here!!</select> <br>
<input type=hidden name=op value=ds>
<input type=submit value=Add>
</form>";
}
else $msg = "Invalid Dealer Name";
}

else if ($_POST[op] == "ds")
{
$dealer_name = $_POST[name];
$dealer_phone = $_POST[phone];
$dealer_fax = $_POST[fax];
$email = $_POST[email];
$website = $_POST[website];
$address = $_POST[address];
$operator = $_POST[operator];
if ($operator == NULL)
{
$row = mysql_fetch_array($sql_res);
$operator1 = stripslashes($row['operator1']);
$operator2 = stripslashes($row['operator2']);
$operator3 = stripslashes($row['operator3']);
$operator4 = stripslashes($row['operator4']);
}
else {
$operator1 = $operator[0];
$operator2 = $operator[1];
$operator3 = $operator[2];
$operator4 = $operator[3];
}
$description = $_POST[description];
$dealer_image = $_POST[image];
$county = $_POST[county];
if ($county == NULL)
{
$row = mysql_fetch_array($sql_res);
$county = stripslashes($row['county']);
}

$query = "replace into dealers values("$dealer_name", "$dealer_phone", "$dealer_fax", "$email", "$website", "$address", "$operator1", "$operator2", "$operator3", "$operator4", "$description", "$image", "$county")";
$query_res = mysql_query($query, $conn) or die(mysql_error());

$msg = "Successfully Edited Dealer $dealer_name";
}
?>
<html>
<head>
<title>EDIT DEALER</title>
</head>
<body>
<center><h2>EDITING DEALER <? echo $dealer_name; ?></h2><p>
<? echo $msg; ?>
</center>
</body>
</html>


The rest of the code in the next POST!

costas
09-06-2006, 03:57 PM
Me again, here's the last bit:

delete.php


<?
function doDB()
{
global $conn;
$conn = mysql_connect("somehost", "username", "password") or die(mysql_error());
mysql_select_db("database_name", $conn) or die(mysql_error());
}

function getdeal()
{
global $sq_res;
$sq = "select * from dealears";
$sq_res = mysql_query($sq, $conn) or die(mysql_error());
}


doDB();
getdeal();

if($_POST[op] != "ds")
{

if (mysql_num_rows($sq_res) >= 1)
{
$msg = "Choose the dealer you want to delete:<br>
<form action=\"$_SERVER[PHP_SELF]\" method=post><select name=name>";
while($row = mysql_fetch_array($sq_res))
{
$dealer_name = stripslashes($row['dealer_name']);
$msg .= "<option value=$dealer_name>$dealer_name</option>";
}
$msg .= "</select><br><input type=hidden name=op value=ds><input type=submit value=Delete></form>";
}
}
else if($_POST[op] == "ds")
{
$dealer_name = $_POST[name];
$sql = "delete from dealers where dealer_name = '$dealer_name'";
$sql_res = mysql_query($sql, $conn) or die(mysql_error());
if($sql_res)
$msg = "Successfully Deleted Dealer $dealer_name";
}
?>
<html>
<head>
<title>DELETE DEALER</title>
</head>
<body>
<center><h2>DELETING DEALER <? echo $dealer_name; ?></h2><p>
<? echo $msg; ?>
</center>
</body>
</html>


Hope it WORKS, because I didn't check! It should work though as I wrote some similar code for a personal project.
Hope I helped!!:)

spider1405
09-06-2006, 05:31 PM
Hi Costas,

I copied all that. I don't understand how to do the main.php page with the links/menu. I tried to go to site/add.php but it threw me an error as well as every page.

Is this because of the main.php page missing??

Thanks a Million,
David

(I'm new at this)

costas
09-07-2006, 10:30 AM
As for the problem that shows you an error. It has nothing to do with main.html/main.php file. You should put in the link the name or IP address of your host: e.g. "http://localhost/add.php" or "http://127.0.0.1/add.php". It works fine for me!:)
Then, as for the main.html(better than .php). The code:

<html>
<head>
<title>Main Page</title>
</head>
<body>
<center><h2>Main Page</h2></center><p>
<ol><li><a href="http://localhost/add.php">Add a New Dealer</a></li>
<li><a href="http://localhost/list.php">See the existing Delaers</a></li>
<li><a href="http://localhost/delete.php">Delete a Dealer??</a></li>
</ol>
</body>
</html>


This should work!!:D

Note: The "localhost" marked with red should be the name or IP Address of your host.

Hope I helped!!!!:) :D