PDA

View Full Version : PHP/MySQL database help


helpme
01-10-2006, 01:57 AM
Hi. I'm really new to Dreamweaver and PHP. I'm normally a FrontPage user. But I'm working on a site that is database driven (my first one ever) so I'm working with DreamWeaver, PHP and MySQL. I have my database set up, and I have no problem displaying the data from a table with DreamWeaver. The table is a list of vehicles, with various makes, models, etc. I want to provide a function to visitors that will allow them to select a specific make, and then a specific model based on the make selected, and show in the results just the vehicles that match the selected make and model. Any assistance with this would be greatly appreciated.

Creative Insanity
01-10-2006, 02:48 AM
You could do that with a selective search. Set a two dynamic lists, one to pick up make from the database and one to pick up models.

helpme
01-12-2006, 01:23 AM
Well, I'm really excited about learning how to create database driven sites, and hopefully once I get the hang of this, I'll be able to create some great sites, but I'm sad to admit, I have no idea what I'm doing. I could still use some help.

I have created 2 lists - one that pulls all of the makes from the table, and one that pulls all of the models from the table. How to I get only the models to display for the make selected? And then how do I get the table to display only those vehicles that match the selected make and model?

Here's my code:
<?php require_once('Connections/Inventory.php'); ?>
<?php
$maxRows_Vehicles = 10;
$pageNum_Vehicles = 0;
if (isset($_GET['pageNum_Vehicles'])) {
$pageNum_Vehicles = $_GET['pageNum_Vehicles'];
}
$startRow_Vehicles = $pageNum_Vehicles * $maxRows_Vehicles;

mysql_select_db($database_Inventory, $Inventory);
$query_Vehicles = "SELECT `Year`, Make, Model, Color, Mileage, Stock, Picture FROM Used_vehicles";
$query_limit_Vehicles = sprintf("%s LIMIT %d, %d", $query_Vehicles, $startRow_Vehicles, $maxRows_Vehicles);
$Vehicles = mysql_query($query_limit_Vehicles, $Inventory) or die(mysql_error());
$row_Vehicles = mysql_fetch_assoc($Vehicles);

if (isset($_GET['totalRows_Vehicles'])) {
$totalRows_Vehicles = $_GET['totalRows_Vehicles'];
} else {
$all_Vehicles = mysql_query($query_Vehicles);
$totalRows_Vehicles = mysql_num_rows($all_Vehicles);
}
$totalPages_Vehicles = ceil($totalRows_Vehicles/$maxRows_Vehicles)-1;

mysql_select_db($database_Inventory, $Inventory);
$query_Make = "SELECT Make FROM Used_vehicles group by Make";
$Make = mysql_query($query_Make, $Inventory) or die(mysql_error());
$row_Make = mysql_fetch_assoc($Make);
$totalRows_Make = mysql_num_rows($Make);

mysql_select_db($database_Inventory, $Inventory);
$query_Model = "SELECT Used_vehicles.Model FROM Used_vehicles group by Model";
$Model = mysql_query($query_Model, $Inventory) or die(mysql_error());
$row_Model = mysql_fetch_assoc($Model);
$totalRows_Model = mysql_num_rows($Model);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form name="form1" method="get" action="">


</p>
<table width="301" border="1">
<tr>
<td width="125">Select Make </td>
<td width="160">Select Model </td>
</tr>
<tr>
<td><select name="select">
<?php
do {
?>
<option value="<?php echo $row_Make['Make']?>"><?php echo $row_Make['Make']?></option>
<?php
} while ($row_Make = mysql_fetch_assoc($Make));
$rows = mysql_num_rows($Make);
if($rows > 0) {
mysql_data_seek($Make, 0);
$row_Make = mysql_fetch_assoc($Make);
}
?>
</select></td>
<td><select name="select2">
<?php
do {
?>
<option value="<?php echo $row_Model['Model']?>"<?php if (!(strcmp($row_Model['Model'], $row_Make['Make']))) {echo "SELECTED";} ?>><?php echo $row_Model['Model']?></option>
<?php
} while ($row_Model = mysql_fetch_assoc($Model));
$rows = mysql_num_rows($Model);
if($rows > 0) {
mysql_data_seek($Model, 0);
$row_Model = mysql_fetch_assoc($Model);
}
?>
</select></td>
</tr>
</table>


</p>


</p>
</form>


</p>
<table border="2">
<tr>
<td>Year</td>
<td>Make</td>
<td>Model</td>
<td>Color</td>
<td>Mileage</td>
<td>Stock</td>
<td>Picture</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Vehicles['Year']; ?></td>
<td><?php echo $row_Vehicles['Make']; ?></td>
<td><?php echo $row_Vehicles['Model']; ?></td>
<td><?php echo $row_Vehicles['Color']; ?></td>
<td><?php echo $row_Vehicles['Mileage']; ?></td>
<td><?php echo $row_Vehicles['Stock']; ?></td>
<td><?php echo $row_Vehicles['Picture']; ?></td>
</tr>
<?php } while ($row_Vehicles = mysql_fetch_assoc($Vehicles)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($Vehicles);

mysql_free_result($Make);

mysql_free_result($Model);
?>

Creative Insanity
01-12-2006, 06:33 AM
Ok to save me creating a heap of database inserts, export your database with phpmyadmin (ask your hoster about that) and then link it and I will code something up using ONLY DW and then try and explain what is going on and post the entire code.
Once you understand the code and look at it you will catch on quicker than if I rambled on here for a day or two.

davidj
01-12-2006, 08:49 AM
Ok to save me creating a heap of database inserts, export your database with phpmyadmin (ask your hoster about that) and then link it and I will code something up using ONLY DW and then try and explain what is going on and post the entire code.


you spoil em CI. you really spoil em

davidj
01-12-2006, 10:49 AM
what i would do here is have 2 dropdowns

dropdown 1 contains makes of car (called in from a table) this will be created from recordset 1

recordset1...
select * from makes
table [makes] contains 2 fields...

[make_ID]
[make_name]

************************

dropdown 2 contains models which is empty to begin with as the recordset2 has a WHERE clause

recordset2...
select * from models where make_ID = '%s', $makeid
table [models] contains 3 fields...

[model_ID]
[make_ID] <<<<<<< allow duplicates
[model_name]

**************************

then you would use a javascript function to submit the form on an onChange attached to the first dropdown

function formsub(){
document.form1.submit();
}

<select name="dropdown1" id="dropdown1" onChange="formsub()">

or have i just copied CI's idea ? soz if i have

Creative Insanity
01-12-2006, 07:13 PM
or have i just copied CI's idea ?
Actually you have just saved me a job ;)
Ta muchly Mr dj sir. *happy happy joy joy*

But if you do not understand that hm then read mine above.

helpme
01-12-2006, 10:50 PM
Thanks to both of you for your help. I will try using davidj's post and see what happens. I'll keep you posted on my progress!

Thanks a bunch!

helpme
01-14-2006, 04:55 PM
I started with a blank PHP page in Dreamweaver. I have set up the 2 tables exactly as described, and created the first recordset - no problem there. I then created a form with dropdown1 using recordset1. Tested that and the makes all show correctly.

Then I tried to create recordset 2, and am getting an error when I try to test it:
select * from models where make_ID = '%s', $makeid

It's a syntax error that refers to the syntax near $makeid.

Creative Insanity
01-14-2006, 05:10 PM
You only need one record set but you make it an advanced one and setup some varibles for it.

davidj
01-16-2006, 08:45 AM
you need 2 recordsets

1 that just displays the dropdown1 info

2 that is to display the contents for dropdown2 which is going to take its where clause from drpdwn1

I suppose you could be clever and use 1 but to simplify it i would sugest 2 Rsets

NOTE:

the example is pesudo code and wont work
select * from models where make_ID = '%s', $makeid

in your second RSET you need to do this using advanced RSET and set your where clause and declare your variables in that remember that you are passing the variable from what ever you select from drpdwn 1

Creative Insanity
01-16-2006, 09:57 AM
Only need one recordset with a join and you could join table IDs for make and model. But then since this is from me I guess that would be considered clever to someone from the UK. LOL

davidj
01-16-2006, 10:06 AM
ok

lets analyse it

RSET 1 shows us all choices for dropdown1
you select an option from RSET1/ dropdown1 that populates dropdown2

lets say dropdown 1 contains 50 car manufacturers and you select porche which sends the porche ID to recordset 2 which displays the variants (911, boxter etc)

please tell me what would be displaying in the first dropdown after you make a selection that you have set your where clause and filtered your RSET if you only had 1 recordset (remember that when i finish with porche i want to lookup ferrari but you wont be able to see that choice as you have filtered the only RSET to display porche's)

??????????????????????????????????????
??????????????????????????????????????
??????????????????????????????????????

Creative Insanity
01-16-2006, 10:15 AM
Ok.. good question. Why would you want to look up holden when you want to find out about Fords? You could always look up ford later on.

My best option here would be to put my theory where my mouth is and create it with one recordset which I will do tomorrow since I am off to bed now.
Old fellas need to be in bed before witching hour since we get up before the birds do. LOL

davidj
01-16-2006, 10:17 AM
ok then its guns at dawn then

i will also develop my 6 gun example.

careful as i shoot from the hip

helpme
01-16-2006, 12:31 PM
You guys are too funny. Well, I got stuck over the weekend, but in working with some other code that I was working on for a forum, I actually think I learned some things. Specifically about setting variables. Yep, that's all new to me. I've mainly only ever worked with html, so this is getting fun for me to learn all this new stuff. I look forward to seeing what you both come up with, and I'm sure I'll need more help.

davidj
01-16-2006, 01:12 PM
dont worry helpme

we will be sweeping up bits of kiwi after i gun him down with my php - AK47

Creative Insanity
01-16-2006, 06:38 PM
i shoot from the hip
I shoot from inside a tank LOL

I am starting now.. some real creative database work needed.. hehe

Creative Insanity
01-16-2006, 08:52 PM
Done with one rs.

and now the details.. to keep this in the format of the site I created this all in DW8 with no external code.

The Index Page

<?php require_once('Connections/search.php'); ?>
<?php
mysql_select_db($database_search, $search);
$query_rsSearch = "SELECT * FROM make";
$rsSearch = mysql_query($query_rsSearch, $search) or die(mysql_error());
$row_rsSearch = mysql_fetch_assoc($rsSearch);
$totalRows_rsSearch = mysql_num_rows($rsSearch);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>--------------------------------------- B O O M !! ----------------------------------------------------</title>
</head>

<body>
<form id="search" name="search" method="post" action="detail.php">
<select name="make" size="1" id="make">
<option value="-">-</option>
<option value="Holden">Holden</option>
<option value="Ford">Ford</option>
</select>
<select name="model" size="1" id="model">
<?php
do {
?>
<option value="<?php echo $row_rsSearch['model']?>"><?php echo $row_rsSearch['model']?></option>
<?php
} while ($row_rsSearch = mysql_fetch_assoc($rsSearch));
$rows = mysql_num_rows($rsSearch);
if($rows > 0) {
mysql_data_seek($rsSearch, 0);
$row_rsSearch = mysql_fetch_assoc($rsSearch);
}
?>
</select>
<input type="submit" name="Submit" value="Hurry Up" />
</form>
</body>
</html>
<?php
mysql_free_result($rsSearch);
?>


The Results Page

<?php require_once('Connections/search.php'); ?>
<?php
$WTmodel_rsResults = "-1";
if (isset($_POST['model'])) {
$WTmodel_rsResults = (get_magic_quotes_gpc()) ? $_POST['model'] : addslashes($_POST['model']);
}
$WTmake_rsResults = "-1";
if (isset($_POST['make'])) {
$WTmake_rsResults = (get_magic_quotes_gpc()) ? $_POST['make'] : addslashes($_POST['make']);
}
mysql_select_db($database_search, $search);
$query_rsResults = sprintf("SELECT * FROM make WHERE make.make = '%s' OR make.model = '%s'", $WTmake_rsResults,$WTmodel_rsResults);
$rsResults = mysql_query($query_rsResults, $search) or die(mysql_error());
$row_rsResults = mysql_fetch_assoc($rsResults);
$totalRows_rsResults = mysql_num_rows($rsResults);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>--------------------------------- Kaaaa B O O M! ---------------------------------</title>
</head>

<body>
<?php do { ?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><?php echo $row_rsResults['make']; ?></td>
</tr>
<tr>
<td><?php echo $row_rsResults['model']; ?></td>
</tr>
<tr>
<td><?php echo $row_rsResults['notes']; ?></td>
</tr>
<tr>
<td><hr /></td>
</tr>
</table>
<?php } while ($row_rsResults = mysql_fetch_assoc($rsResults)); ?></body>
</html>
<?php
mysql_free_result($rsResults);
?>


The Database

CREATE TABLE `make` (
`mkID` mediumint(10) NOT NULL auto_increment,
`make` varchar(50) NOT NULL default '',
`model` varchar(50) NOT NULL default '',
`notes` varchar(255) NOT NULL default '',
PRIMARY KEY (`mkID`)
) TYPE=MyISAM AUTO_INCREMENT=12 ;


INSERT INTO `make` VALUES (1, 'Ford', 'XR8', 'a car');
INSERT INTO `make` VALUES (2, 'Holden', 'Monaro', 'real fast car');
INSERT INTO `make` VALUES (4, 'Ford', 'Focus', 'car');
INSERT INTO `make` VALUES (5, 'Ford', 'Fusion', 'car');
INSERT INTO `make` VALUES (6, 'Ford', 'Tourus', 'car');
INSERT INTO `make` VALUES (7, 'Ford', 'falcon', 'car');
INSERT INTO `make` VALUES (8, 'Holden', 'Commadore', 'car');
INSERT INTO `make` VALUES (9, 'Holden', 'Banina', 'car');
INSERT INTO `make` VALUES (10, 'Holden', 'Vectra', 'car');
INSERT INTO `make` VALUES (11, 'Holden', 'Berlina', 'car');

and it was easy also. Oh BTW.. no extenstions where used either

and a working copy ('http://webthings.org/search/').. when selecting model place make on the - selection.

davidj
01-17-2006, 01:57 PM
ahh ic

im thinking from an application developer's perspective where all dropdowns are dynamic which is where i got the first recordset. i rarely do hard coded <options>

cant see the wood for the trees.


i would bin the seperate results page and just use the one coz that way your dropdowns are on the page all the time

you win this time CI but i will be back <_<

Creative Insanity
01-17-2006, 08:28 PM
yeah ok I could make them both dynamic, I was just too lazy to look all through DW to see how I could get repeated names not to show. Like in the make field there was many ford and holden and I could not find in DW a way to get only one ford and one holden to show and it showed all of them repeatedly. Which is why I cheated sorta.

As for the same page.. yeah also possible with DW with a condistion, but honestly I never thought of that. Good point.
It is not about winning dj, it was about showing a member how it can be done with DW and nothing more.

helpme
01-18-2006, 03:06 AM
Thanks for the help, but I have one issue with CI's code. What I want is for the 2nd list to be filtered based on the selections from the first list. So if I select Ford, just the Ford model's are displayed in the 2nd list. But that is great and a huge help. I think I'm starting to understand some more.

Creative Insanity
01-18-2006, 06:15 AM
If that is the case and what you want I don't see the point in the model choice. If you pick a model it can be a model from any car.

Honestly if that is what you want I really cannot see the point of the model selection.

davidj
01-18-2006, 08:51 AM
ok

here is my version

database and table creation...

# EMS MySQL Manager Lite 3.3.0.4
# ---------------------------------------
# Host : localhost
# Port : 3306
# Database : cars


SET FOREIGN_KEY_CHECKS=0;

CREATE DATABASE `cars`

USE `cars`;

#
# Structure for the `car_types` table :
#

CREATE TABLE `car_types` (
`car_type` varchar(25) NOT NULL,
`car_variant` varchar(30) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Data for the `car_types` table (LIMIT 0,500)
#

INSERT INTO `car_types` (`car_type`, `car_variant`) VALUES
('Aston','db5'),
('Aston','db6'),
('Aston','vanquish'),
('Alfa','147'),
('Alfa','156'),
('Alfa','166'),
('Alfa','GT'),
('Alfa','GTV'),
('Audi','A3'),
('Audi','A4'),
('Audi','A6'),
('BMW','3 series'),
('BMW','5 series'),
('BMW','6 series'),
('Porche','boxter'),
('Porche','911'),
('Porche','carrera');

COMMIT;

php page...

<?php require_once('Connections/cars.php'); ?>
<?php
$car = "x";
if (isset($_POST['cars'])) {
$car = $_POST['cars'];
}

mysql_select_db($database_cars, $cars);
$query_Recordset1 = sprintf("SELECT * FROM car_types WHERE car_types.car_type = '%s'", $car);
$Recordset1 = mysql_query($query_Recordset1, $cars) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);


?>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>


</head>

<body>
<form name="form1" method="post" action="cars.php">
<select name="cars" id="cars" onChange="document.form1.submit()">
<option selected value="" <?php if (!(strcmp("x", $car))) {echo "SELECTED";} ?>>Select Car </option>
<option value="Aston" <?php if (!(strcmp("Aston", $car))) {echo "SELECTED";} ?>>Aston</option>
<option value="Alfa" <?php if (!(strcmp("Alfa", $car))) {echo "SELECTED";} ?>>Alfa</option>
<option value="Audi" <?php if (!(strcmp("Audi", $car))) {echo "SELECTED";} ?>>Audi</option>
<option value="BMW" <?php if (!(strcmp("BMW", $car))) {echo "SELECTED";} ?>>BMW</option>
<option value="Porche" <?php if (!(strcmp("Porche", $car))) {echo "SELECTED";} ?>>Porche</option>
</select>
<select name="models" id="models">

<?php
do {
?>
<option value="<?php echo $row_Recordset1['car_variant']?>"<?php if (!(strcmp($row_Recordset1['car_variant'], $row_Recordset1['car_variant']))) {echo "SELECTED";} ?>><?php echo $row_Recordset1['car_variant']?></option>

<?php
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
$rows = mysql_num_rows($Recordset1);
if($rows > 0) {
mysql_data_seek($Recordset1, 0);
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
}
?>

</select>
</form>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>

Connections ....

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_cars = "localhost";
$database_cars = "cars";
$username_cars = "root";
$password_cars = "password";
$cars = mysql_pconnect($hostname_cars, $username_cars, $password_cars) or trigger_error(mysql_error(),E_USER_ERROR);
?>

this example uses 1 page and displays car makes and types

Just create your db and change any username and passwords for your db in the connections file

davidj
01-18-2006, 09:06 AM
Honestly if that is what you want I really cannot see the point of the model selection.


Dynamic drop downs example ('http://www.personalcarfinance.co.uk/afp/carquote.afp?cat=new')

Creative Insanity
01-18-2006, 09:57 AM
That example is a good one dj I like that.
If that is the one you done then I hand over the trophy to you.
Way cool.

helpme
01-19-2006, 02:29 AM
I've almost got it. I'm just modifying the code to my database that I already have set up, and I probably won't have much time until this weekend, but I do think I'm starting to understand it now. Next step is getting a recordset of all of the vehicles that match the selections in the dropdowns. I'll keep you posted.

BTW - I'm using davidj's code. Thanks again!

helpme
01-22-2006, 11:51 PM
So, I gave up on selecting model, and just went with giving users the option of filtering based on model. That was the only way I could get the correct results to show. Thank you both for your help.

http://www.josephneri.com/used_vehicles_filter.php is the final result.

Next project...register/login for my forum that I was able successfully create.

Creative Insanity
01-23-2006, 12:51 AM
Yeah that is ok.. but lower the resolution of your thumbs.. they take too long to load.
You want a fast result in previews other wise you are wasting bandwidth on displaying large images resized.
Create thumbs.

davidj
01-23-2006, 02:25 PM
here is a thumb i created earlier

domedia
01-23-2006, 03:14 PM
ROFL! :mrgreen:

Creative Insanity
01-23-2006, 04:45 PM
Oh f... dj is here again. Ya know where that thumb can go don't ya dj. LOL