Ad:

PHP Discussion Thread
Unsolved
Views: 868

Sep 27th, 2008

Use mysql data to form links in PHP

Expand Post »

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

Similar Threads

PHP Form Validation ??? in PHP
Realtime input field update in JavaScript / DHTML / AJAX

forwardlookguy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

23 posts
since Jun 2008

Permalink

Sep 27th, 2008

Re: Use mysql data to form links in PHP

Click to Expand / Collapse Quote 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

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.

R0bb0b

Reputation Points: 358

Solved Threads: 89

Posting Shark

Offline

986 posts
since Jun 2008

Permalink

Sep 28th, 2008

Re: Use mysql data to form links in PHP

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

forwardlookguy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

23 posts
since Jun 2008

Permalink

Sep 28th, 2008

Re: Use mysql data to form links in PHP

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:

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

forwardlookguy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

23 posts
since Jun 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Displaying date from mysql

Next Thread in PHP Forum Timeline: regarding image

DaniWeb Message

Cancel Changes

User Name
Password