Multiple SQL Queries

[webweever]

Is there another way I can do what this code is doing? I can do a separate SQL query for ever state but there has to be a more efficient way.

 

<?php 
			$sql = 'SELECT name FROM markers WHERE state = "AL"';
			$result = mysql_query($sql) or trigger_error('Select SQL failed.', E_USER_ERROR);
			$num_rows = mysql_num_rows($result);
			echo 'Alabama<br/>';
			if ($num_rows < 1) {
				echo "No tracks in this state"; 
			}else {
				while ($row = mysql_fetch_assoc($result)) {
				echo '<a href="test.php?name=' . $row['name'] . '">' . $row['name'] . '</a><br/> ';
				} 
			}
		?>
<br/><br/>
		<?php 
			$sql = 'SELECT name FROM markers WHERE state = "AK"';
			$result = mysql_query($sql) or trigger_error('Select SQL failed.', E_USER_ERROR);
			$num_rows = mysql_num_rows($result);
			echo 'Alaska<br/>';
			if ($num_rows < 1) {
				echo "No tracks in this state"; 
			}else {
				while ($row = mysql_fetch_assoc($result)) {
				echo '<a href="test.php?name=' . $row['name'] . '">' . $row['name'] . '</a><br/> ';
				} 
			} 

		?>
<br/><br/>
		<?php 
			$sql = 'SELECT name FROM markers WHERE state = "AZ"';
			$result = mysql_query($sql) or trigger_error('Select SQL failed.', E_USER_ERROR);
			$num_rows = mysql_num_rows($result);
			echo 'Arizona<br/>';
			if ($num_rows < 1) {
				echo "No tracks in this state"; 
			}else {
				while ($row = mysql_fetch_assoc($result)) {
				echo '<a href="test.php?name=' . $row['name'] . '">' . $row['name'] . '</a><br/> ';
				} 
			}			?>
<br/><br/>
		<?php 
			$sql = 'SELECT name FROM markers WHERE state = "AR"';
			$result = mysql_query($sql) or trigger_error('Select SQL failed.', E_USER_ERROR);
			$num_rows = mysql_num_rows($result);
			echo 'Arkansas<br/>';
			if ($num_rows < 1) {
				echo "No tracks in this state"; 
			}else {
				while ($row = mysql_fetch_assoc($result)) {
				echo '<a href="test.php?name=' . $row['name'] . '">' . $row['name'] . '</a><br/> ';
				} 
			}
		?>

[Mark Baker]

If you have a list of all 50 states in a second table on your database, then you can run a single query that joins the states table with your markers table.

[webweever]

Correct me if I'm wrong but even if I had another table with all the states in it I'd still have to write a seperate statement for each state similar to this;

SELECT *  
FROM markers 
    LEFT JOIN states
ON markers.stateID = states.stateID
WHERE states.state = 'Arizona';

 

That would give me everything with Arizona set as the state but I'm still going to have to do the same thing for the other 49 states correct?

[joel24]

I don't really know exactly how your site is running,

but wouldn't you be better off having a "track" column in the markers table, tinyInt 1/0.

and if the state has a track, set it to 1, otherwise set it to 0?

 

and then mysql "select name from markers where track = 1"

[webweever]

What I want to do is create a page that has each track separated by state like this:

 

Alabama:

a list of tracks in Alabama here

 

Arizona:

a list of tracks in Arizona here

 

This is what my marker table looks like:

CREATE TABLE `markers` (
  `marker_id` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL default '',
  `street` varchar(200) NOT NULL default '',
  `city` varchar(100) NOT NULL default '',
  `state` varchar(100) NOT NULL default '',
  `zip` varchar(100) NOT NULL default '0',
  `url` varchar(200) NOT NULL default '',
  `lat` float(10,6) NOT NULL default '0.000000',
  `lng` float(10,6) NOT NULL default '0.000000',
  `cat_id` int(11) NOT NULL default '0',
  `marker_cat` varchar(200) NOT NULL default '',
  `type` varchar(100) NOT NULL default '',
  `length` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`marker_id`)
) TYPE=InnoDB AUTO_INCREMENT=258 ;

 

So, if I query that state field I can produce a list based on that value. Problem is the only way I can think of to do that is to write a separate SQL query for each state.

[joel24]

maybe have a states table also...?

and then when you add tracks, you select a state...

 

and the states table would look like

 

stateID,

name,

abbreviation,

hasTrack.

 

and then pre-populate that state table with your 50 states, and when you add a track, the drop down menu for states will be populated from that table, and have the value as the stateID and then if that state's hasTrack column is 0, it will be set to 1.

 

then when you list your states you can say "SELECT name FROM states WHERE hasTrack = 1";

....

then in the markers column link it to the state with stateID... i spose the state abbreviations will would work just aswell as ID numbers.

 

does that make sense?

[Mark Baker]

Correct me if I'm wrong but even if I had another table with all the states in it I'd still have to write a seperate statement for each state similar to this

You shouldn't need to. I'm not a mysql expert, and I'm sure others could give you the correct query.

 

But try something like:

SELECT states.state, 
        markers.name  
  FROM states 
  LEFT JOIN markers
    ON markers.stateID = states.stateID