Cleaning up my queries

[Strangen]

I am new to PHP and was wondering what I could do to cleanup my code? Seems like I have a lot of unecessary code.

                 <?php
include "dbinfo.php";
$sql1 = "SELECT `category` FROM `categories` WHERE `id`=1";
$sql2 = "SELECT `category` FROM `categories` WHERE `id`=2";
$sql3 = "SELECT `category` FROM `categories` WHERE `id`=3";
$sql4 = "SELECT `category` FROM `categories` WHERE `id`=4";

$catid1 = mysql_query($sql1,$db);
$catid2 = mysql_query($sql2,$db);
$catid3 = mysql_query($sql3,$db);
$catid4 = mysql_query($sql4,$db);

$row1 = mysql_fetch_array($catid1);
$cat1 = $row1['category'];
$row2 = mysql_fetch_array($catid2);
$cat2 = $row2['category'];
$row3 = mysql_fetch_array($catid3);
$cat3 = $row3['category'];
$row4 = mysql_fetch_array($catid4);
$cat4 = $row4['category'];
   echo "<table width='100%' border='1'>";
    echo "<tr>";
         echo"<th width='25%' scope='col'><div align='center'>" . $cat1 ." </div></th>";
         echo"<th width='25%' scope='col'><div align='center'>" . $cat2 ." </div></th>";
         echo"<th width='25%' scope='col'><div align='center'>" . $cat3 ." </div></th>";
         echo"<th width='25%' scope='col'><div align='center'>" . $cat4 ." </div></th>";
          echo "</tr><tr><td>";

mysql_close($db); ?>

[mac_gyver]

your goal for database queries is to run one (or as few as possible) query that get the rows you want in the order that you want them.

 

for what you have shown, assuming you only want those specific id values and that there are other id values in the table that you don't want -

$query = "SELECT `category` FROM `categories` WHERE `id` IN(1,2,3,4) ORDER BY id";

if you in fact want all the rows -

$query = "SELECT `category` FROM `categories` ORDER BY id";

after you have selected the row(s) you want in the order that you want them, simply output them the way you want when you loop over them.

[Strangen]

your goal for database queries is to run one (or as few as possible) query that get the rows you want in the order that you want them.

 

for what you have shown, assuming you only want those specific id values and that there are other id values in the table that you don't want -

$query = "SELECT `category` FROM `categories` WHERE `id` IN(1,2,3,4) ORDER BY id";

if you in fact want all the rows -

$query = "SELECT `category` FROM `categories` ORDER BY id";

after you have selected the row(s) you want in the order that you want them, simply output them the way you want when you loop over them.

 

Thanks for the quick reply.

 

Ok I only have 4 values in that table, which I plan to keep only 4 at all times.  So if I use the first query you provided how do I echo the value for each of the categories? I would like to keep $cat1,$cat2,$cat3, and $cat4 as my category variables

[DavidAM]

I would like to keep $cat1,$cat2,$cat3, and $cat4 as my category variables

No, you really don't want to keep them. This is how nightmares begin.

 

If you need those values later in the code, then you want to use an array.

 

<?php
include "dbinfo.php";
$sql = 'SELECT category FROM categories WHERE id IN (1,2,3,4)';
$res = mysql_query($sql, $db);	# You should switch to mysqli (improved) as the mysql library is deprecated
$catList = array();

while ($row = mysql_fecth_array($res)) {
	$catList[] = $row['category'];
}

echo "<table width='100%' border='1'>";
echo "<tr>";
foreach($catList as $cat) {
	echo"<th width='25%' scope='col'><div align='center'>" . $cat ." </div></th>";
}
echo "</tr><tr><td>";
# You didn't close the table - is there more to come?
mysql_close($db);	# It is usually NOT necessary to close the database connection it will close when the script exits
?>

Using an array will make it much easier if you later decide to add a fifth category (or even to remove one of the four). In the code above (which does exactly the same as the original code you posted), to add or remove a category, we simply change one line, the query. With your code, you would have a lot more work.

 

If you need to associate the category with the ID (in later code), then select the ID and the category: SELECT id, category from ..., then use the ID as the index of the array $catList[$row['id']] = $row['category'];.

[Strangen]

No, you really don't want to keep them. This is how nightmares begin.

 

If you need those values later in the code, then you want to use an array.

 

<?php
include "dbinfo.php";
$sql = 'SELECT category FROM categories WHERE id IN (1,2,3,4)';
$res = mysql_query($sql, $db);	# You should switch to mysqli (improved) as the mysql library is deprecated
$catList = array();

while ($row = mysql_fecth_array($res)) {
	$catList[] = $row['category'];
}

echo "<table width='100%' border='1'>";
echo "<tr>";
foreach($catList as $cat) {
	echo"<th width='25%' scope='col'><div align='center'>" . $cat ." </div></th>";
}
echo "</tr><tr><td>";
# You didn't close the table - is there more to come?
mysql_close($db);	# It is usually NOT necessary to close the database connection it will close when the script exits
?>

Using an array will make it much easier if you later decide to add a fifth category (or even to remove one of the four). In the code above (which does exactly the same as the original code you posted), to add or remove a category, we simply change one line, the query. With your code, you would have a lot more work.

 

If you need to associate the category with the ID (in later code), then select the ID and the category: SELECT id, category from ..., then use the ID as the index of the array $catList[$row['id']] = $row['category'];.

 

 

Thank you for you help!  I will give this a try.  I am still learning. 

 

Also the table does continue and I am running queries from other tables, but the code is not as a big of a mess as the code i posted here (Well atleast in my opinion)

 

This is just a simple script for personal offline use only so I am not too worried about my code being a little screwy, but I did want it easier to read. So thanks again for your help!