using foreach with mysql (i think)

[aminkorea]

Hi, I am new to php, mysql and have made some progress, but recently ran into a wall. I hope someone can help me. I am building a simple links script that has categories and a list of links with website name, url and a brief description.  I have two tables-categories and links.

 

I have gotten the back end finished and can add, edit and delete links and categories. The problem is in the front end.  I have the script set up so that when I click on a category name a page is dynamically created using ?id=idnumber.  and the category name is echoed on the page. But I can't seem to find the solution I need to get the links that have a matching category name to display. I have tried using JOIN, LEFT JOIN, INNER JOIN, and RIGHT JOIN and get the same result, ALL the links in the links table are displayed.

 

I think I need to create a new array and use the foreach loop to accomplish this, but I am not sure. Nor am I sure how to use the foreach even though I have read several tutorials on it. 

 

Here is the code I have so far. Any help would be appreciated. Thanks in advance.

 

<? include "db.php" ?>
<?php
$id = intval($_GET['id']); // force user input to an int
if(!empty($id)) // if the user input is not empty or 0
{
$query = "SELECT * FROM categories WHERE id =  $id  LIMIT 1"; // attempt to select the row for the given id
$result = mysql_query($query) or die(mysql_error());

if(mysql_num_rows($result) > 0) // if the categorie was found
{
$row = mysql_fetch_assoc($result); // fetch the resultset to an array called $row

echo $row['categories']; // echo the categories field from the row
// etc.
}
else
{
die('Error: Bad ID'); // the categories was not found
}
}
else
{
die('Error: Bad ID'); // the id given was not valid
}
mysql_close();
?>
<? include "db.php" ?>
<?php
$sql="SELECT * FROM links,categories ORDER BY links.categories LIMIT 10";
$result=mysql_query($sql);

?>
<table width="80%" align="center" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="100%" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="3"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>URL</strong></td>
<td align="center"><strong>Content</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><? echo $rows['name']; ?></td>
<td><a href="<? echo $rows['url']; ?>"><? echo $rows['url']; ?></a></td>
<td><? echo $rows['content']; ?></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>

 

[Psycho]

You shouldn't need to use a JOIN at all. You don't show the structure of the tales, but I would assume that the category table would have a primary, unique ID field and that the links table would have a foreign key field for each record to associate back to the category table. So, if you have the category ID you would just need to query the links table WHERE catID = $id

[aminkorea]

Thanks for the quick reply.  Here is the table structures.  I have a  PRIMARY KEY in both tables, but no UNIQUE or FOREIGN KEY.  So, if I add a foreign key of links.categories and reference it to the id in categories, would I then be able to call the specific links for each category?

 

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categories` varchar(37) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=40 ;

CREATE TABLE IF NOT EXISTS `links` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `categories` varchar(65) NOT NULL DEFAULT '',
  `name` varchar(255) NOT NULL DEFAULT '',
  `url` varchar(255) NOT NULL DEFAULT '',
  `content` varchar(255) NOT NULL DEFAULT '',
  `date_added` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ;

[aminkorea]

I altered my tables and added a unique key on categories.categories and a foreign key of links.categories referencing the categories.categories and get the same results as before I altered the tables.

[Psycho]

OK, I'm not sure how you used the new foreign key, but looking at your original table structures you are doing this wrong. In the 'links' table you have a field called 'categories' which is a varchar. I guess you were populating that field with the name of the category. That's not how you should be associating the links with the categories. You should be associating the links with the categories using ONLY the category ID. That way you can change names in the category table (or any other data associated with a category) and it does not affect the association with the links.

 

For example, let's say you have a category called "Tech Sites" and you used the name to associates the links with the category. Later, you decide you would rather spell out the name to be "Technology Sites". If you changed the name in the categories table you would have to also change all the associations in the links table. Also, associating data with an int field is much more efficient than text.

 

As to table structure:

1. I prefer to use more descriptive names for ID fields than just ID, because you may have many different ID fields in your database. And this way, you can use the same name of the field when used as a foreign key in another table. In fact, if you have the same "type" of field in multiple tables (e.g. a 'name') then it is helpful to give them a precursor in the name.

2. I would not give a field the same name as the table - it leads to confusion.

3. A date field should not be a varchar. In fact you can create a date field such that the default value is the current timestamp, so when the record is created you don't have to specify a value in your query - it is set automatically just like an auto-increment field is!

4. I also notice you have an int field for the category ID with a length of 11, but the links table id only has a length of 4. Not sure why you set it that way, but I don't think the length property has any bearing on what values an int field will take. It is determined by the int type. For example a tinyint can take a value of 0-256 (if unsigned) or -127 to 127, if signed. I would use unsigned ints for primary keys.

5. Foreign keys should typically be indexed so when used for JOINS you don't have performance issues.

6. Your tables should be built something like this

 

CREATE TABLE `categories` (
  `cat_id` tinyint(2) unsigned NOT NULL auto_increment,      -- support up to 256 categories
  `cat_name` varchar(37) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`cat_id`)
)

CREATE TABLE `links` (
  `link_id` mediumint(6) unsigned NOT NULL auto_increment,  -- support up to 8+ million links
  `cat_id` tinyint(2) unsigned NOT NULL,
  `link_name` varchar(255) collate latin1_general_ci NOT NULL,
  `url` varchar(255) collate latin1_general_ci NOT NULL default '',
  `content` varchar(255) collate latin1_general_ci NOT NULL default '',
  `date_added` timestamp NULL default CURRENT_TIMESTAMP,    --Automatically set when records are created
  PRIMARY KEY  (`link_id`),
  KEY `cat_id` (`cat_id`)
)

 

Now, when a user selects a category, you only need to pass the category ID and then query the links table using something like

SELECT `link_name`, `url`, `content`
FROM `links`
WHERE `cat_id` = $catID
ORDER BY `link_name`

[aminkorea]

Thanks Psycho,

 

I had come to the same conclusion about the table structure. I knew the structure was not correct, but this is my first php, mysql project, so I wasn't sure exactly where I had messed up. Like I said in my original post, I got the back end running and everything worked the way I wanted. It wasn't until I started working on the front end of the site that I realized that I had screwed up somewhere. I was hoping for an easier solution, but it looks like I am going to have to start over and see if I can get it right this time. It will take a few day, but I will give your suggestions a try and see what happens. 

 

Thanks again, I will post my results after I have made the changes and let you know how everything turns out. 

 

Cheers.

[aminkorea]

OK, I'm not sure how you used the new foreign key, but looking at your original table structures you are doing this wrong. In the 'links' table you have a field called 'categories' which is a varchar. I guess you were populating that field with the name of the category. That's not how you should be associating the links with the categories. You should be associating the links with the categories using ONLY the category ID. That way you can change names in the category table (or any other data associated with a category) and it does not affect the association with the links.

 

For example, let's say you have a category called "Tech Sites" and you used the name to associates the links with the category. Later, you decide you would rather spell out the name to be "Technology Sites". If you changed the name in the categories table you would have to also change all the associations in the links table. Also, associating data with an int field is much more efficient than text.

 

As to table structure:

1. I prefer to use more descriptive names for ID fields than just ID, because you may have many different ID fields in your database. And this way, you can use the same name of the field when used as a foreign key in another table. In fact, if you have the same "type" of field in multiple tables (e.g. a 'name') then it is helpful to give them a precursor in the name.

2. I would not give a field the same name as the table - it leads to confusion.

3. A date field should not be a varchar. In fact you can create a date field such that the default value is the current timestamp, so when the record is created you don't have to specify a value in your query - it is set automatically just like an auto-increment field is!

4. I also notice you have an int field for the category ID with a length of 11, but the links table id only has a length of 4. Not sure why you set it that way, but I don't think the length property has any bearing on what values an int field will take. It is determined by the int type. For example a tinyint can take a value of 0-256 (if unsigned) or -127 to 127, if signed. I would use unsigned ints for primary keys.

5. Foreign keys should typically be indexed so when used for JOINS you don't have performance issues.

6. Your tables should be built something like this

 

CREATE TABLE `categories` (
  `cat_id` tinyint(2) unsigned NOT NULL auto_increment,      -- support up to 256 categories
  `cat_name` varchar(37) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`cat_id`)
)

CREATE TABLE `links` (
  `link_id` mediumint(6) unsigned NOT NULL auto_increment,  -- support up to 8+ million links
  `cat_id` tinyint(2) unsigned NOT NULL,
  `link_name` varchar(255) collate latin1_general_ci NOT NULL,
  `url` varchar(255) collate latin1_general_ci NOT NULL default '',
  `content` varchar(255) collate latin1_general_ci NOT NULL default '',
  `date_added` timestamp NULL default CURRENT_TIMESTAMP,    --Automatically set when records are created
  PRIMARY KEY  (`link_id`),
  KEY `cat_id` (`cat_id`)
)

 

Now, when a user selects a category, you only need to pass the category ID and then query the links table using something like

SELECT `link_name`, `url`, `content`
FROM `links`
WHERE `cat_id` = $catID
ORDER BY `link_name`

 

Well, I had a few extra hours so, I tried changing the table structure and the respective code. Unfortunately, the changes actually made things worse. So changed everything back.  Am not sure where to go from here.

[Psycho]

Well, I had a few extra hours so, I tried changing the table structure and the respective code. Unfortunately, the changes actually made things worse. So changed everything back.  Am not sure where to go from here.

 

Well, if you built code to work with your current DB design you're not going to be able to change the DB and have the code still work. Your method is referencing links to categories is incorrect. I am not willing to help you implement a solution for a flawed design. The changes I suggested can be done in multiple phases - not all at once. So, you can do one change, fix the code sections that would need modifications due to that change, then move to the next change.

 

Here is my suggestion:

 

1. Change the date_added field to a timestamp field that defaults to CURRENT_TIMESTAMP. Then, find any code where you are setting the value and remove it. I assume you have a single query for INSERTing new links. Just remove the code that creates the date value and remove the date_added portions of the insert query. You will also need to find any code sections that use that value. Since the value is currently a string and you are changing it to a timestamp you will need to preformat the value before display. The value will be returned from the database in the format 'YYYY-MM-DD HH:MM:SS'. All you need to do is determine the format you want the value to be displayed to the user and use the date() and strtotime() functions.

$display_date = date('F j, Y', strtotime($dateFromDB));
echo "Link was created on $displayDate";  //Output: Link was created on March 8, 2012

 

Then do the same process for other fields one or two at a time . . .

 

2. Change the two fields in the categories table and find/update all code that references those field names. No logic changes should be needed.

 

3. Change the links id field and again change all references to the field name. No logic changes should be needed

 

4. Lastly change the 'categories' field in the links table to 'cat_id". This will probably take the most changes to the code. Just look at one process at a time: creating links will need to insert the cat_id instead of the name, retrieving links will need to use the cat_id, etc.

 

If you want to zip up your current code and attach it here I *might* take a look, but I promise nothing.

[aminkorea]

Psycho,

 

I appreciate the time and effort, but your code isn't working.  I change the date_added as you suggested to

 

 `date_added` timestamp NULL default CURRENT_TIMESTAMP,    

 

Then I inserted the code you gave me into list_records.php in the admin (This is the only place I have the date added called from the db)

 

The result was nothing. No error, no date displayed, just a blank column. 

 

 

 

[aminkorea]

Just thought I should add that I have dropped the date_added row from the table. The date added is not important right now. What's important is that I get the content in the links table to display under the corrent category. I'm going to have a go at Psyco's table again and see if I can get it to work.

[Psycho]

Psycho,

 

I appreciate the time and effort, but your code isn't working.  I change the date_added as you suggested to

 

 `date_added` timestamp NULL default CURRENT_TIMESTAMP,    

 

Then I inserted the code you gave me into list_records.php in the admin (This is the only place I have the date added called from the db)

 

The result was nothing. No error, no date displayed, just a blank column. 

 

So, let's review:

 

1. You changed the date_added field to an actual date field with a default value of CURRENT_TIMESTAMP

2. You then modified the list_records.php to display the date based upon the value from the database and you saw nothing.

 

Well, there are a couple problems there. First of all, all you did was change the database field. That will not update the existing values for that field to the current timestamp. The default value is only set when new records are created. Second, I'm pretty sure that if you change an existing field to a date field, if the current values are not already in the proper format the values will default to 00-00-0000 or some other base value. Therefore, the php code provided previously for formatting the date should default to 1970 [unix Epoch (January 1 1970 00:00:00 GMT)]. If you are getting NO output, then you did something wrong. maybe you defined a variable using

$display_date = date('F j, Y', strtotime($dateFromDB));

but didn't actually use $display_date for the output.

 

You are making a bunch of changes and not seeing the results you expect so assume it is wrong. Stop, and debug your code to check what actually is happening.

[aminkorea]

Hey Psyco,

 

When I added the date to the table, I chose CURRENT TIMESTAMP update Current Timestamp.  The sql table, as you said, defaulted to 0000.00.00. I then added the code that you provided and I got the default on the page. So, I went back to the table settings and changed it to DATE with update currenet timestamp.  The page then went blank. I tried a couple other variations of date fields and I remember that at  one point I got a default date of 1970. I didn't know that the field set it self according to the UNIX  Epoch date until after I had removed the coded and altered my tables to values that you suggested(minus the date_added row).

 

But, I didn't just give up because it didn't work.  I just put more priority on getting the content to display than the date. I worked with the code for the new tables all day yesterday and for a few hours today, and I still haven't got it to work, but I think I am closer. 

 

I will try to do some debugging tomorrow, but I will have to study up on how to debug first.  Please keep in mind that I have only begun to learn this language and there is a lot to learn. 

 

Thanks for your input and advise.

[aminkorea]

Back again.  I have worked out most of the bugs and can now add and list records in both the categories and the links table. But I haven't yet figured out how to get the links table to grab the cat_id that identifies the category name (cat_name)

 

I am using a drop down list on the add and update scripts. I select the category that the link goes into and click submit. All the information is then inserted into the links table, except the cat_id. The cat_id always inserts as 0. 

 

The structure of my tables is

 

`categories`;
CREATE TABLE IF NOT EXISTS `categories` (
  `cat_id` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(37) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;

`links`;
CREATE TABLE IF NOT EXISTS `links` (
  `link_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
  `cat_id` tinyint(2) unsigned NOT NULL,
  `cat_name` varchar(255) NOT NULL,
  `link_name` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `content` varchar(255) NOT NULL,
  PRIMARY KEY (`link_id`),
  KEY `cat_id` (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

 

I use the following form to add to the links table.

 

<?php
$result = mysql_query("SELECT cat_id,cat_name FROM categories") or die(mysql_error()); 
while ($row = mysql_fetch_array($result)) {
$cat_id=$row["cat_id"];
$cat_name=$row["cat_name"];
$options.= '<option value="'.$row['cat_name'].'">'.$row['cat_name'].'</option>';
};
?>
<?php
mysql_close();
?>


<title>Links add to table form add.php</title>
<center>
<form id="addtolinks" action="added.php" method="post" name="addtolinks">
<table width="448" border="0" cellspacing="2" cellpadding="0">
<tr><td width="150">
<input id="cat_id" name="cat_id" type="hidden" valuse="">
</td></tr>
<tr><td width="150">
<div align="right">
<label for="categories">Category Name</label>
</div>
</td><td><select id="cat_name" name="cat_name"><options><?php echo $options; ?></select"> </td>  
</tr><tr>
<td width="150">
<div align="right">
<label for="name">Website Name</label>
</div>
</td>
<td>
<input id="link_name" name="link_name" type="text" size="25" value="Site Name" maxlength="25">
</td>
</tr><tr>
<td width = "150">
<div align="right">
<label for="url">Website URL</label>
</div>
</td>
<td>
<input id="url" name="url" type="text" size="25" value="http://" maxlength="100">
</td>
</tr><tr>
<td width = "150">
<div align="right">
<label for="content">A brief Description</label>
</div>
</td>
<td>
<textarea id="content" name="content" rows="4" cols="40">
</textarea></td></tr><tr><td width="150"></td><td>
<input type="submit" name="submitButtonName" value="Add">
</td>
</tr></table>
</form>
</center>

 

and this is the code that actually inserts the information that is submitted.

 

$query="INSERT INTO links ('links.cat_id')
SELECT
    cat_id
FROM
    categories
WHERE
    categories.cat_name = links.cat_name;"
?>
<?
$link_id =trim($post['link_id']);
$cat_id = trim($_POST['cat_id']);
$cat_name = trim($_POST['cat_name']);
$link_name = trim($_POST['link_name']);
$url = trim($_POST['url']);
$content = trim($_POST['content']);
$query = "INSERT INTO links (link_id, cat_id, cat_name, link_name, url, content)
VALUES ('', '$cat_id','$cat_name', '$link_name', '$url', '$content')";
$results = mysql_query($query);

if ($results)
{
echo "Details added.";
}
mysql_close();
?>

 

I am not getting any errors. And everything is being inserted except the cat_id. So, how do I get the cat_id from the categories table to get added to the links table when the category is selected in the submit form?  What am I missing? 

 

Again, thanks in advance for any help.

 

Cheers

[Psycho]

You need to change the select list for choosing the category. The VALUE of the options should be the category ID and the LABEL will be the category name. Also your current code assigns the values for id and name to variables from the $row array, but you don't even use them.

$result = mysql_query("SELECT cat_id, cat_name FROM categories") or die(mysql_error()); 
while ($row = mysql_fetch_array($result))
{
    $options.= "<option value='{$row['cat_id']}'>{$row['cat_name']}</option>\n";
}

 

Then in the form, you have tow fields. One for cat_name and one for cat_id. Remove the one for cat_id and change the one for category name so that the display to the user is still category name, but the input (a select list) should use the name cat_id.

<form id="addtolinks" action="added.php" method="post" name="addtolinks">
<table width="448" border="0" cellspacing="2" cellpadding="0">
  <tr>
    <td width="150">
      <div align="right"><label for="categories">Category Name</label></div>
    </td>
    <td>
      <select id="cat_id" name="cat_id">
      <?php echo $options; ?>
      </select">
    </td>  
  </tr>

 

Then, in the form processing code, you would use the value from this field in the insert query. And, remove the freakin' cat_name field from the links table. It defeats the whole purpose of a relational database. you are also wide open to malicious users corrupting/deleting your database. You need to always sanitize user input. Lastly, why do you hav a link_id in the form input? That should be generated automatically in the database.

$cat_id = intval($_POST['cat_id']);
$link_name = mysql_real_escape_string(trim($_POST['link_name']));
$url = mysql_real_escape_string(trim($_POST['url']));
$content = mysql_real_escape_string(trim($_POST['content']));
$query = "INSERT INTO links (cat_id, link_name, url, content)
VALUES ('$cat_id', '$link_name', '$url', '$content')";

[aminkorea]

Sorry for the lengthy time between posts, but been busy with other stuff.  Psycho, I did exactly as you said using the table structure you provided and it didn't work. I finally gave up and returned to my original table structure. However, your did provide the missing part I was looking for. I made a few modifications to my table and the code and it now works like I had intended. My table structure is now

 

--
-- Table structure for table `categories`
--

DROP TABLE IF EXISTS `categories`;
CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categories` varchar(37) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=40 ;

-- --------------------------------------------------------

--
-- Table structure for table `links`
--

DROP TABLE IF EXISTS `links`;
CREATE TABLE IF NOT EXISTS `links` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `catid` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `url` varchar(255) NOT NULL DEFAULT '',
  `content` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `catid` (`catid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=35 ;

 

As you can see, I changed 'categories' in my original links table to catid and added a foreign key. The I changed the code in my php files to

 

 

<? include "db.php" ?>
<?php
$id = intval($_GET['id']); // force user input to an int
if(!empty($id)) // if the user input is not empty or 0
{
$query = "SELECT * FROM categories WHERE id =  $id  LIMIT 1"; // attempt to select the row for the given id
$result = mysql_query($query) or die(mysql_error());

if(mysql_num_rows($result) > 0) // if the categorie was found
{
$row = mysql_fetch_assoc($result); // fetch the resultset to an array called $row

echo $row['categories']; // echo the categories field from the row
// etc.
}
else
{
die('Error: Bad ID'); // the categories was not found
}
}
else
{
die('Error: Bad ID'); // the id given was not valid
}
mysql_close();
?>
<? include "db.php" ?>
<?php
$id = $_GET['id'];
$sql="SELECT name,url,content FROM links where catid = $id";
$result=mysql_query($sql);

?>
<table width="80%" align="center" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="100%" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="3"><strong>List data from mysql </strong> </td>
</tr>

<tr>
<td align="center"><strong>Name</strong></td>
<td align="center"><strong>URL</strong></td>
<td align="center"><strong>Content</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result)){
?>
<tr>
<td><? echo $rows['name']; ?></td>
<td><a href="<? echo $rows['url']; ?>"><? echo $rows['url']; ?></a></td>
<td><? echo $rows['content']; ?></td>
</tr>
<?php
}
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>

 

Now when I click a category from the list of categories in list_categories.php, list_records.php displays the category and any links posted in that category.  Problem  solved.

 

Thanks for your help.  Cheers