Jump to content

Archived

This topic is now archived and is closed to further replies.

opel

2 questions about list code

Recommended Posts

I've been getting good answers with my PHP beginners problems so I will try again with this one:

I have created an input form for a links management system which also allows the user to input info about a link. This form includes a dynamic list that is fed from the category DB. After looking through some other posts on the forum I managed to get my code to work on a page of its own but there are 2 problems:

1. when I include the while loop in the page like it is below nothing displays except the default option, I think for some reason the while loop is not triggering.

2. the list when I test it on page without the HTML it works but displays a link category for EVERY link in the DB not just 1 instance of each category, how do I set a limiter in my loop? have attached a png of what happens

As always appreciate any help.

[code]<div id="main">
<h1>Add Link</h1>
<p>Please fill out the details below to add a link to the database. <strong>All of the fields must be completed before the form can be submitted.</strong></p>

<div style="width:100%">
<?php
if(isset($_POST['add']))
{
include ('connections/config.php');
include ('connections/dbconn.php');

$linkTitle = $_POST['inpTitle'];
$linkDesc = $_POST['inpDescription'];
$linkUrl = $_POST['inpLinkUrl'];
$linkCategory = $_POST['inpCat'];
$linkDisplay = $_POST['inpDisplay'];

$query = "INSERT INTO tbl_links (link_url, link_title, link_desc, category_id, link_display) VALUES ( '$linkUrl', '$linkTitle', '$linkDesc', '$linkCategory', '$linkDisplay')";
mysql_query($query) or die('Error, insert query failed');


$result = ("SELECT * FROM categories, tbl_links WHERE categories.category_id = tbl_links.category_id ORDER BY category_name");
$catResult = mysql_query($result) or die("error querying database");
$row = mysql_fetch_array($catResult);

//Put at the bottom of the PHP statement
include ('connections/dbclose.php');
echo "<p class='message'>Your Links have been successfully added, click here to <a href='add_link.php'>add another link</a></p>";
}
else
{
?>
<form method="post">
<table cellpadding="5">
<tr>
<th width="120">Field Name</th>
<th width="378">Field Inputs</th>
</tr>
<tr>
<td width="120"><div align="right"><strong>Category :</strong></div></td>
<td><select>
<?php
echo "<option value=''>Select one</option>";
while($row = mysql_fetch_assoc($catResult)){
echo "<option value='$row[link_id]'>$row[category_name]</option>";
}
?>
</select> </td>
</tr>
<tr>
<td width="120"><div align="right"><strong>Title :</strong></div></td>
<td><input type="text" name="inpTitle" class="inputbox" size="72" /></td>
</tr>
<tr>
<td width="120"><div align="right"><strong>Description :</strong></div></td>
<td><input type="text" name="inpDescription" class="inputbox" size="72" /></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td width="120"><div align="right"><strong>Link URL <em>('http;//' not required)</em> :</strong></div></td>
<td><input type="text" name="inpLinkUrl" value="www.sitename.com" class="inputbox" size="72" /></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td width="120"><div align="right"><strong>Display Link :</strong></div></td>
<td><input name="inpDisplay" type="checkbox" class="inputbox" value="Yes" checked="CHECKED"/></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td width="120"><div align="right"><strong>Click to submit :</strong></div></td>
<td><input type="submit" value="Add This Link" name="add" id="add"/></td>
</tr>
</table>
</form>
<?
}
?>
</div><!--tablediv-->
</div><!--main--->[/code]

[attachment deleted by admin]

Share this post


Link to post
Share on other sites
echo "<option value='$row[[color=red]"[/color]link_id[color=red]"[/color]]'>$row[[color=red]"[/color]category_name[color=red]"[/color]]</option>";


Not to steady on this one, but give it a go:

$result = ("SELECT DISTINCT categories.category_name, tbl_links.link_id FROM categories, tbl_links WHERE categories.category_id = tbl_links.category_id ORDER BY categories.category_name");

Share this post


Link to post
Share on other sites
you need to use some sort of 'JOIN' in your mysql 'SELECT' statement.  Whats probably happening is that its getting all the records from your 'categories' table AND all the records from your 'tbl_links' table where 'categories.category_id = tbl_links.category_id'.  A join would help although my knowledge on mysql joins isnt that wonderful.  Possibly something like:-

"SELECT * FROM categories LEFT JOIN tbl_links ON categories.category_id = tbl_links.category_id;

You also need to move the database connection outside and above the if statement because if your 'else' is true there wont be a database connection to get the records.  Also you need to move:-

[code]

$result = ("SELECT * FROM categories, tbl_links WHERE categories.category_id = tbl_links.category_id ORDER BY category_name");
$catResult = mysql_query($result) or die("error querying database");
$row = mysql_fetch_array($catResult);


[/code]

out of the 'if' to inside the 'else' otherwise the query wont be executed, resulting in 0 records to loop though and resulting in a blank drop down box.

Also in your html option tags you are setting the viewable text to the 'category' value of each link record so you will only get the categories displayed in the drop down list box.  Not sure what you want to display in the list box as you havent really stated whether its ment to be the category or the link or some other information stored in your link table

Share this post


Link to post
Share on other sites
I am trying to get my drop down box to display a a list of 1 instance of available link categories, have attached screen example.

I understand where I have gone wrong with my SQL by pulling out a the list from my links table as opposed to my catgeory table. I dont understand where my SQL statements should be though. The if else statement is handling everything for the input form, should I put the drop down SQL in separate delimiters?

[attachment deleted by admin]

Share this post


Link to post
Share on other sites
Ahhh, if you only want the categories to display in the drop down list box just have a select statement that selects all records from the categories table only and order them by category name.

Your code with the mysql statements moved would look something like this.

[code]

<div id="main">
<h1>Add Link</h1>
<p>Please fill out the details below to add a link to the database. <strong>All of the fields must be completed before the form can be submitted.</strong></p>

<div style="width:100%">
<?php

include ('connections/config.php');
include ('connections/dbconn.php');

if(isset($_POST['add']))
{

$linkTitle = $_POST['inpTitle'];
$linkDesc = $_POST['inpDescription'];
$linkUrl = $_POST['inpLinkUrl'];
$linkCategory = $_POST['inpCat'];
$linkDisplay = $_POST['inpDisplay'];

$query = "INSERT INTO tbl_links (link_url, link_title, link_desc, category_id, link_display) VALUES ( '$linkUrl', '$linkTitle', '$linkDesc', '$linkCategory', '$linkDisplay')";
mysql_query($query) or die('Error, insert query failed');

echo "<p class='message'>Your Links have been successfully added, click here to <a href='add_link.php'>add another link</a></p>";

}
else
{
?>
<form method="post">
<table cellpadding="5">
<tr>
<th width="120">Field Name</th>
<th width="378">Field Inputs</th>
</tr>
<tr>
<td width="120"><div align="right"><strong>Category :</strong></div></td>
<td><select>
<?php

$result = "SELECT * FROM categories ORDER BY category_name";
$catResult = mysql_query($result) or die("error querying database");
$row = mysql_fetch_array($catResult);

echo "<option value=''>Select one</option>";
while($row = mysql_fetch_assoc($catResult)){
echo "<option value='$row[link_id]'>$row[category_name]</option>";

}

//Put at the bottom of the PHP statement
include ('connections/dbclose.php');

?>
</select> </td>
</tr>
<tr>
<td width="120"><div align="right"><strong>Title :</strong></div></td>
<td><input type="text" name="inpTitle" class="inputbox" size="72" /></td>
</tr>
<tr>
<td width="120"><div align="right"><strong>Description :</strong></div></td>
<td><input type="text" name="inpDescription" class="inputbox" size="72" /></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td width="120"><div align="right"><strong>Link URL <em>('http;//' not required)</em> :</strong></div></td>
<td><input type="text" name="inpLinkUrl" value="www.sitename.com" class="inputbox" size="72" /></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td width="120"><div align="right"><strong>Display Link :</strong></div></td>
<td><input name="inpDisplay" type="checkbox" class="inputbox" value="Yes" checked="CHECKED"/></td>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
<td width="120"><div align="right"><strong>Click to submit :</strong></div></td>
<td><input type="submit" value="Add This Link" name="add" id="add"/></td>
</tr>
</table>
</form>
<?
}
?>
</div><!--tablediv-->
</div><!--main--->

[/code]

Share this post


Link to post
Share on other sites
when I inserted that script my page crashed, see example.

I think we are getting there as I tested the script on a blank PHP page and it worked but only displayed one of the category links?

Will I not have to use the inner join statement you mentioned above so that I can linke to the '$row[link_id]' for use in the insert form.

Thanks for your help.

[attachment deleted by admin]

Share this post


Link to post
Share on other sites
Ok I have nearly fixed it think I just have a simple problem with my SQL as my drop down is displaying the same category name more than once for some reason instead of just once instance of each.

here is my working code with the dodgy SQL statement:

<select name="inpCat">
  <?php


mysql_select_db($dbname, $conn);
$query_rsLinks = "SELECT * FROM categories, tbl_links WHERE categories.category_id = tbl_links.category_id ORDER BY categories.category_name";
$rsLinks = mysql_query($query_rsLinks, $conn) or die(mysql_error());
$row_rsLinks = mysql_fetch_assoc($rsLinks);
$totalRows_rsLinks = mysql_num_rows($rsLinks);
 
do { 
?>
  <option value="<?php echo $row_rsLinks['category_id']?>"><?php echo $row_rsLinks['category_name']?></option>
  <?php
} while ($row_rsLinks = mysql_fetch_assoc($rsLinks));
  $rows = mysql_num_rows($rsLinks);
  if($rows > 0) {
      mysql_data_seek($rsLinks, 0);
  $row_rsLinks = mysql_fetch_assoc($rsLinks);
  }

include ('../connections/dbclose.php');
?>

</select>

[attachment deleted by admin]

Share this post


Link to post
Share on other sites
Ok I have nearly fixed it think I just have a simple problem with my SQL as my drop down is displaying the same category name more than once for some reason instead of just once instance of each.

here is my working code with the dodgy SQL statement:

[code]<select name="inpCat">
  <?php


mysql_select_db($dbname, $conn);
$query_rsLinks = "SELECT * FROM categories, tbl_links WHERE categories.category_id = tbl_links.category_id ORDER BY categories.category_name";
$rsLinks = mysql_query($query_rsLinks, $conn) or die(mysql_error());
$row_rsLinks = mysql_fetch_assoc($rsLinks);
$totalRows_rsLinks = mysql_num_rows($rsLinks);
 
do { 
?>
  <option value="<?php echo $row_rsLinks['category_id']?>"><?php echo $row_rsLinks['category_name']?></option>
  <?php
} while ($row_rsLinks = mysql_fetch_assoc($rsLinks));
  $rows = mysql_num_rows($rsLinks);
  if($rows > 0) {
      mysql_data_seek($rsLinks, 0);
  $row_rsLinks = mysql_fetch_assoc($rsLinks);
  }

include ('../connections/dbclose.php');
?>

</select>[/code]

[attachment deleted by admin]

Share this post


Link to post
Share on other sites
Fixed it, here is solution should anyone else come across this:

[code]<select name="inpCat">
  <?php


mysql_select_db($dbname, $conn);
$query_rsLinks = "SELECT * FROM categories ORDER BY category_name ASC";
$rsLinks = mysql_query($query_rsLinks, $conn) or die(mysql_error());
$row_rsLinks = mysql_fetch_assoc($rsLinks);
$totalRows_rsLinks = mysql_num_rows($rsLinks);
 
do { 
?>
  <option value="<?php echo $row_rsLinks['category_id']?>"><?php echo $row_rsLinks['category_name']?></option>
  <?php
} while ($row_rsLinks = mysql_fetch_assoc($rsLinks));
  $rows = mysql_num_rows($rsLinks);
  if($rows > 0) {
      mysql_data_seek($rsLinks, 0);
  $row_rsLinks = mysql_fetch_assoc($rsLinks);
  }

include ('../connections/dbclose.php');
?>

</select>[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.