Need help.
Hi everybody I am really stuck. I am creating a php website with mysql interaction. User can upload images and assign categories to them, user create their own categories, for example Wedding, Birthday, Baby etc. One image can belong to several categories. So I have a nice database for this. Anyway when browsing images the user is supposed to be able to select which categories he would like to see, can be more then one, so he would like to see Wedding and Birthday photos for example. This is my selection form. First I get the user categories from the database. Then I create an html form with tickboxes.
$result = getcategories();
<form method='post' action='http://localhost/dissertation/functions/Image/index_browse.php'>
<table bgcolor='#cccccc'>
<tr><td>All</td>
<?php
$num_results = $result->num_rows;
for ($i=0; $i <$num_results; $i++)
{
$row = $result->fetch_assoc();
$Category_Name = $row['Category_Name'];
echo "<td>$Category_Name</td>";
}
echo "</tr><tr><td><input type='checkbox' name='all' value='all' /></td>";
$result = getcategories();
$num_results = $result->num_rows;
for ($i=0; $i <$num_results; $i++)
{
$row = $result->fetch_assoc();
$Category_Name = $row['Category_Name'];
echo "<td><center><input type='checkbox' name='checkbox[]' value='$Category_Name' /></center></td>";
}
?>
<td><input type="submit" name="Submit" value="Submit" /></td></tr>
</table></form>
When the user clicks a tick or several I get an array ('checkbox) of the size of all his categories with the Category name depending if the tickbox is selected or not IE:
Array checkbox {
Value =
Value = Wedding
Value =
Value = Birthday
Value =
}
My problem is that I need to write the SQL statement, and the sql statement changes every time the user selects different button combinations, moreover the sql statement users pagination as well.
Select * from image,category,categories
where Category.category_ID = (Category.category_ID=8 or Category.category_ID=9)
and Category.User_Name='simon'
and Category.Category_ID = Categories.Category_id
and Categories.Image_ID = Image.Image_ID and Image.Image_Edit=1 LIMIT $from, $max_results
What I need to write dynamicalled is the first where statement =
where Category.category_ID = one or more category ID’s
Now I was thinking of just writing the sql statement and depending if a checkbox was ticked append to the sql string
$sql = $sql ."Category.category_ID=$Category_ID"
The obvious problem is that for the first array value WHICH CONTAINS a value I need to append a “(“ IF I have more then one tickbox selected. If I have several values I need to write a “or” between them. For the last array item I need to write a ), if the user only ticked 1 checkbox I will not use the (Category.category_ID=8 or Category.category_ID=9) method I need to write where = Category.category_ID=9.
Also the page can be loaded or refreshed without clicking a submit button so I have a check if submit was pressed.
Now this is way to complicated… I know there must be an easier way… HELP ME HELP!!!!
This is what I tried to write:
if (isset($submit) && $submit == 'Submit')
{
//If All images are supposed to be displayed
if (isset($all_images) && $all_images == 'all'))
{
$sql = "select * from Image where User_Name='$username' and Image.Image_Edit=1 LIMIT $from, $max_results";
$_SESSION['sql']=$sql;
}
else
{
$sql = "Select * from image,category,categories where ";
$array_size = count($checkbox_images);
for ($i=0;$i<$array_size;$i++)
{
$value = $checkbox_images[$i];
if (empty($value))
{
Category.category_ID = (
}
else
{
if (isset($sqltwo))
{
$Category_ID = get_categoryID($value,$username);
$sql = $sql ."Category.category_ID=$Category_ID"
}
else
{
$Category_ID = get_categoryID($value,$username);
$sql2 = $sql ."Category.category_ID=$Category_ID"
}
}
}
$sql = $sql .")and Category.User_Name='$username' and Category.Category_ID = Categories.Category_id
and Categories.Image_ID = Image.Image_ID and Image.Image_Edit=1 LIMIT $from, $max_results";
}
}
//If the submit button was not pressed
else
{
$sql = "select * from Image where User_Name='$username' and Image.Image_Edit=1 LIMIT $from, $max_results";
}
