DaniWeb Forum Index > Web Development > PHP

Use mysql data to form links in PHP

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2008

Posts: 23

Reputation: forwardlookguy is an unknown quantity at this point

Solved Threads: 0

forwardlookguy forwardlookguy is offline

Offline

Newbie Poster

Use mysql data to form links in PHP

Sep 27th, 2008

Hey everyone, I want to use mysql to list all of the makes of vehicles and I want the user to be able to click a make and the years that make were made pop up. Is there a way to do this using PHP? I've got the makes to list out but I can't figure out how to make them into a link that takes you to a page of the all the years it was offered.

Thanks for any help!
Arthur

•

Join Date: Jun 2008

Posts: 849

Reputation: R0bb0b is on a distinguished road

Solved Threads: 67

R0bb0b

Offline

Practically a Posting Shark

Re: Use mysql data to form links in PHP

Sep 27th, 2008

•

Originally Posted by forwardlookguy

Hey everyone, I want to use mysql to list all of the makes of vehicles and I want the user to be able to click a make and the years that make were made pop up. Is there a way to do this using PHP? I've got the makes to list out but I can't figure out how to make them into a link that takes you to a page of the all the years it was offered.

Thanks for any help!
Arthur

I would start with something like this, it is untested so may need a little debugging, but not much

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
<? include("connection.php"); ?>
<!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>Untitled Document</title>
</head>
 
<body>
<?
$query = "";
if(isset($_GET['make']))
{
	if(trim($query) == "")
	{
		$query .= " where make = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
	else
	{
		$query .= " and make = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
}
 
if(isset($_GET['year']) && $_GET['year'] != "--select--")
{
	if(trim($query) == "")
	{
		$query .= " where year = '" . mysql_real_escape_string(stripslashes($_GET['year'])) . "'";
	}
	else
	{
		$query .= " and year = '" . mysql_real_escape_string(stripslashes($_GET['year'])) . "'";
	}
}
 
$query = "select vehiclepk, make, model, year from vehicles" . $query . " order by make, model";
$searchresult = mysql_query($query);
 
$query = "select distinct make, year from vehicles order by make, year";
$yearresult = mysql_query($query);
 
$yeararray = array();
while($row = mysql_fetch_assoc($yearresult))
{
	$yeararray[$row["make"]][] = $row["year"];
}
?>
	<table cellpadding="0" cellspacing="0">
	<?
		if(mysql_num_rows($searchresult) == 0 || !is_numeric(mysql_num_rows($searchresult)))
		{
		?>
			<tr>
				<td colspan="3">No vehicles found under that search criteria.</td>
			</tr>
		<?
		}
		else
		{
			for($i = 0; $i < mysql_num_rows($searchresult); $i++)
			{
			?>
				<tr>
					<td><a href="<? echo basename($_SERVER['PHP_SELF']); ?>?make=<? echo urlencode(mysql_result($searchresult, $i, "make")); ?>"><? echo mysql_result($searchresult, $i, "make"); ?></a></td>
					<td><? echo mysql_result($searchresult, $i, "model"); ?></td>
					<td>
						<form action="<? echo basename($_SERVER['PHP_SELF']); ?>" method="get">
						<input type="hidden" name="make" value="<? echo mysql_result($searchresult, $i, "make"); ?>" />
						<select name="year" id="year<? echo mysql_result($searchresult, $i, "vehiclepk"); ?>">
							<option value="--select--">--select--</option>
							<?
							foreach($yeararray[mysql_result($searchresult, $i, "make")] as $value)
							{
							?>
								<option value="<? echo $value; ?>"><? echo $value; ?></option>
							<?
							}
						?>
						</select>&nbsp;&nbsp;
						<input type="submit" value="Search" />
						</form>
					</td>
				</tr>	
			<?
			}
		}
	?>
	</table>
</body>
</html>
<?
mysql_close();
?>
<? include("connection.php"); ?>
<!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>Untitled Document</title>
</head>

<body>
<?
$query = "";
if(isset($_GET['make']))
{
	if(trim($query) == "")
	{
		$query .= " where make = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
	else
	{
		$query .= " and make = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
}

if(isset($_GET['year']) && $_GET['year'] != "--select--")
{
	if(trim($query) == "")
	{
		$query .= " where year = '" . mysql_real_escape_string(stripslashes($_GET['year'])) . "'";
	}
	else
	{
		$query .= " and year = '" . mysql_real_escape_string(stripslashes($_GET['year'])) . "'";
	}
}

$query = "select vehiclepk, make, model, year from vehicles" . $query . " order by make, model";
$searchresult = mysql_query($query);

$query = "select distinct make, year from vehicles order by make, year";
$yearresult = mysql_query($query);

$yeararray = array();
while($row = mysql_fetch_assoc($yearresult))
{
	$yeararray[$row["make"]][] = $row["year"];
}
?>
	<table cellpadding="0" cellspacing="0">
	<?
		if(mysql_num_rows($searchresult) == 0 || !is_numeric(mysql_num_rows($searchresult)))
		{
		?>
			<tr>
				<td colspan="3">No vehicles found under that search criteria.</td>
			</tr>
		<?
		}
		else
		{
			for($i = 0; $i < mysql_num_rows($searchresult); $i++)
			{
			?>
				<tr>
					<td><a href="<? echo basename($_SERVER['PHP_SELF']); ?>?make=<? echo urlencode(mysql_result($searchresult, $i, "make")); ?>"><? echo mysql_result($searchresult, $i, "make"); ?></a></td>
					<td><? echo mysql_result($searchresult, $i, "model"); ?></td>
					<td>
						<form action="<? echo basename($_SERVER['PHP_SELF']); ?>" method="get">
						<input type="hidden" name="make" value="<? echo mysql_result($searchresult, $i, "make"); ?>" />
						<select name="year" id="year<? echo mysql_result($searchresult, $i, "vehiclepk"); ?>">
							<option value="--select--">--select--</option>
							<?
							foreach($yeararray[mysql_result($searchresult, $i, "make")] as $value)
							{
							?>
								<option value="<? echo $value; ?>"><? echo $value; ?></option>
							<?
							}
						?>
						</select>&nbsp;&nbsp;
						<input type="submit" value="Search" />
						</form>
					</td>
				</tr>	
			<?
			}
		}
	?>
	</table>
</body>
</html>
<?
mysql_close();
?>

just edited the second loop

Last edited by R0bb0b; Sep 27th, 2008 at 5:44 pm.

“Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.” - Dr. Seuss

-- The documentation is inevitable, you may get away with it for a little while but eventually you too will have to do the deed.

•

Join Date: Jun 2008

Posts: 23

Reputation: forwardlookguy is an unknown quantity at this point

Solved Threads: 0

forwardlookguy forwardlookguy is offline

Offline

Newbie Poster

Re: Use mysql data to form links in PHP

Sep 28th, 2008

Awesome! Thanks for posting but I still have a slight problem. I got it to echo the Makes out and when I click them, it takes me back to the page. However, I want the page to be where you click on the make from the list and it takes you to another page where the years for that make are listed and you can click on those. How would I go about doing that? I'm sure your code is easily modified to accomplish this but I can't figure it out. Btw, I have a database called productionfigures with tables called Make and Years. They have a common id linking them. For instance, Chevrolet has the id of 1 in the Makes table and 1930-2008 have the id of 1 as well in the Years table and so on.

I hope this isn't too confusing.

Thanks for your time,
Arthur

•

Join Date: Jun 2008

Posts: 23

Reputation: forwardlookguy is an unknown quantity at this point

Solved Threads: 0

forwardlookguy forwardlookguy is offline

Offline

Newbie Poster

Re: Use mysql data to form links in PHP

Sep 28th, 2008

Ok, I've been working on this code and have gotten it figured out except for one thing. I cannot populate the drop down boxes with the years that are specific to the makes. Here's the modified code:

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
 
<?php
 
require "includes/dbconnect.php";
 
 
$query = "";
if(isset($_GET['make']))
{
	if(trim($query2) == "")
	{
		$query2 .= " where name = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
	else
	{
		$query2 .= " and name = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
}
 
if(isset($_GET['year']) && $_GET['year'] != "--select--")
{
	if(trim($query3) == "")
	{
		$query3 .= " where yearname = '" . mysql_real_escape_string(stripslashes($_GET['year'])) . "'";
	}
	else
	{
		$query3 .= " and yearname = '" . mysql_real_escape_string(stripslashes($_GET['year'])) . "'";
	}
}
 
$query2 = "select id, name from makes" . $query2 . " order by name";
$searchresult = mysql_query($query2) or die(mysql_error());;
 
$query3 = "select distinct yearname from years order by yearname";
$yearresult = mysql_query($query3) or die(mysql_error());;
 
 
$yeararray = array();
while($row = mysql_fetch_assoc($yearresult))
{
	$yeararray[$row["make"]][] = $row["year"];
}
?>
	<table cellpadding="0" cellspacing="0">
	<?
		if(mysql_num_rows($searchresult) == 0 || !is_numeric(mysql_num_rows($searchresult)))
		{
		?>
			<tr>
				<td colspan="3">No vehicles found under that search criteria.</td>
			</tr>
		<?
		}
		else
		{
			for($i = 0; $i < mysql_num_rows($searchresult); $i++)
			{
			?>
				<tr>
					<td><a href="<? echo basename($_SERVER['PHP_SELF']); ?>?make=<? echo urlencode(mysql_result($searchresult, $i, "name")); ?>"><? echo mysql_result($searchresult, $i, "name"); ?></a></td>
					<td><? echo mysql_result($yearresult, $i, "yearname"); ?></td>
					<td>
						<form action="<? echo basename($_SERVER['PHP_SELF']); ?>" method="get">
						<input type="hidden" name="make" value="<? echo mysql_result($searchresult, $i, "name"); ?>" />
						<select name="year" id="year<? echo mysql_result($yearresult, $i, "yearname"); ?>">
							<option value="--select--">--select--</option>
							<?
							foreach($yeararray[mysql_result($yearresult, $i, "yearname")] as $value)
							{
							?>
								<option value="<? echo $value; ?>"><? echo $value; ?></option>
							<?
							}
						?>
						</select>&nbsp;&nbsp;
						<input type="submit" value="Search" />
						</form>
					</td>
				</tr>	
			<?
			}
		}
 
?>
	</table>
</body>
</html>
<?
mysql_close();
?>
<?php

require "includes/dbconnect.php";


$query = "";
if(isset($_GET['make']))
{
	if(trim($query2) == "")
	{
		$query2 .= " where name = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
	else
	{
		$query2 .= " and name = '" . mysql_real_escape_string(stripslashes($_GET['make'])) . "'";
	}
}

if(isset($_GET['year']) && $_GET['year'] != "--select--")
{
	if(trim($query3) == "")
	{
		$query3 .= " where yearname = '" . mysql_real_escape_string(stripslashes($_GET['year'])) . "'";
	}
	else
	{
		$query3 .= " and yearname = '" . mysql_real_escape_string(stripslashes($_GET['year'])) . "'";
	}
}

$query2 = "select id, name from makes" . $query2 . " order by name";
$searchresult = mysql_query($query2) or die(mysql_error());;

$query3 = "select distinct yearname from years order by yearname";
$yearresult = mysql_query($query3) or die(mysql_error());;


$yeararray = array();
while($row = mysql_fetch_assoc($yearresult))
{
	$yeararray[$row["make"]][] = $row["year"];
}
?>
	<table cellpadding="0" cellspacing="0">
	<?
		if(mysql_num_rows($searchresult) == 0 || !is_numeric(mysql_num_rows($searchresult)))
		{
		?>
			<tr>
				<td colspan="3">No vehicles found under that search criteria.</td>
			</tr>
		<?
		}
		else
		{
			for($i = 0; $i < mysql_num_rows($searchresult); $i++)
			{
			?>
				<tr>
					<td><a href="<? echo basename($_SERVER['PHP_SELF']); ?>?make=<? echo urlencode(mysql_result($searchresult, $i, "name")); ?>"><? echo mysql_result($searchresult, $i, "name"); ?></a></td>
					<td><? echo mysql_result($yearresult, $i, "yearname"); ?></td>
					<td>
						<form action="<? echo basename($_SERVER['PHP_SELF']); ?>" method="get">
						<input type="hidden" name="make" value="<? echo mysql_result($searchresult, $i, "name"); ?>" />
						<select name="year" id="year<? echo mysql_result($yearresult, $i, "yearname"); ?>">
							<option value="--select--">--select--</option>
							<?
							foreach($yeararray[mysql_result($yearresult, $i, "yearname")] as $value)
							{
							?>
								<option value="<? echo $value; ?>"><? echo $value; ?></option>
							<?
							}
						?>
						</select>&nbsp;&nbsp;
						<input type="submit" value="Search" />
						</form>
					</td>
				</tr>	
			<?
			}
		}

?>
	</table>
</body>
</html>
<?
mysql_close();
?>

As it is now, one year is listed beside the make and then there is the drop down box with --select-- in it and the submit button. Can anyone tell me what I've missed here?

Thanks,
Arthur