PHP-problem: INNER Join and Photo upload

General PHP Help
#1

Hi, I’m about to build a website where people can upload items for sale and have run into a few problems.

I’ll try to be as specific as possible;
I have a problem with the search field where I previously have been able to combine the search field with the belonging category for the product. If you would leave the search field blank (keywords) and specify the category (drop-down menu) the result would show everything that has been registered to that category and vice versa. If you leave the category menu unspecified and type a keyword the you’d find result with that keyword in every category. Leave all empty and it shows all uploaded ads.

This is what my code looks like [php]

<?php $search = $_GET["search"]; $cat = $_GET["cat"]; $city = $_GET["city"]; if($search!="" &&$cat=="0") $sql = "SELECT tblad.*, category.catName FROM tblad INNER JOIN category ON category.catID=category.catID WHERE Object LIKE '%$search%' ORDER BY DateTime DESC"; if($search!="" &&$cat!="0") $sql = "SELECT tblad.*, category.catName FROM tblad INNER JOIN category ON tblad.catID=category.catID WHERE Object LIKE '%$search%' AND tblad.catID=$cat ORDER BY DateTime DESC"; if($search=="" && ($cat!="0"&&$cat!="")) $sql = "SELECT tblad.*, category.catName FROM tblad INNER JOIN category ON tblad.catID=category.catID WHERE tblad.catID=$cat ORDER BY DateTime DESC"; if($sql =="") $sql = "SELECT tblad.*, category.catName FROM tblad INNER JOIN category ON tblad.catID=category.catID ORDER BY DateTime DESC LIMIT 90"; echo "
"; echo "
";[/php] Afterwards I realised I wanted people to be able to specify where they live, city wise. I chose to create a table in the db that i named city. I later created cityID and cityName within that table. In this way I want people to be able to search geographically. Now to the problem at hand: How would I go about to insert a third search choice (drop-down menu for cities containing the names) into the searching section. How would I make these three independent of each other but at the same time dependent so that the results could get either narrower or broader. I'm thinking in such a manner as it has been done previously but at that time with 2 search choices (keyword search and category drop down). When results are showing, I've come in to a problem where under the city results the cityID is showing instead of cityName and I keep getting errors when I try to write the cityName as echo. I think this will go away when I know more about how to fetch the information from the problem above, but it could be of interest to know about this now. To problem number 969:P: Uploading a picture to an ad. It refuses to show. I'm unsure if this is because of where the pictures are save or if the error is within the code. When I fill in everything, choose a picture and submit i get the following error: ""Array ( [filFile] => Array ( [name] => 649.jpg [type] => image/jpeg [tmp_name] => /Applications/XAMPP/xamppfiles/temp/phpCt1GKN [error] => 0 [size] => 9013 ) ) [u]Your ad was successfully uploaded! Click [b]here[/b] to take a look at it""[/u] The underlined is what is echoed if everything goes according to plan. And it does go according to plan if I check the database, just that the picture isn't saved/showing(its a question mark) or alternatively going somewhere I don't know (i specified the location as "pics/". Also I have no idea where "/Applications/XAMPP/xamppfiles/temp/" comes from. I've tried typing that directory as a destination but without any luck. I have no problem uploading whatever the community requires in order to get as good overview of the problem as possible. I would however love to have someone more experience in PHP to skype-textchat with in real time about problems since this is something I'm really starting to take huge interest in! I've been at this for some time now and I'm not getting any wiser :( Have a great one, ModiPad
#2

Ok first you got some issues in your first query.

[php] if($search!="" &&$cat==“0”) $sql = “SELECT tblad.*, category.catName FROM tblad INNER JOIN category ON category.catID=category.catID WHERE Object LIKE ‘%$search%’ ORDER BY DateTime DESC”; [/php]

Basically, you’re joining category.catid to itself

[php]category.catID=category.catID [/php]

That makes no sense…

Your second, third, and Forty query looks OK, I’m guessing Object? Is a column in the tblad database?

Next question, do you have a cityID column in the tblad database? You’re going to need to be able to link the City table to you ad table.

#3

Thank’s a lot, yeah I fixed it to
[php]tblad.catID=category.catID[/php].

However since these 4 querys only combine the search field and the category drop-down, I am looking for either another query combination in order to combine a third city-drop-down-menu into the queries (got a table named city with cityID and cityName).

And yes, I do have a cityID column in the tblad database. But no adid (Aid) in my city(table) database, this shouldn’t be aproblem since the category database doesnt have aid but the tblad has catID.

By the way, thank you very much for taking a look at my problem!

#4

It’s not that hard to combine all your queries, first you write SQL a lot differently then I do. Which is not a bad thing, I just think the way you do it is more complicated. Personal Choice.

I never spell out INNER JOIN, RIGHT JOIN, LEFT JOIN etc, it’s just to much typing for me…

Let’s start off with your base query.

[php]SELECT * FROM tblad, city, category where tblad.catID=category.catID and tblad.cityId = city.cityid[/php]

Now we have all 3 of your tables joined, if you run that query, you should get everything back.

Since we have the base query all we need to do now is build the where clause based on what the user selects.

[php]
$search = $_GET[“search”];
$cat = $_GET[“cat”];
$city = $_GET[“city”];

$sql = “SELECT * FROM tblad, city, category where tblad.catID=category.catID and tblad.cityId = city.cityid “;
if ($search!=””)
{
$sql += " Object LIKE ‘%{$search}%’ ";
}

if ($cat!=“0”)
{
$sql += " tblad.catID={$cat} ";
}

if ($city !="")
{
$sql += " city.cityID={$city} ";
}

$sql += " ORDER BY DateTime DESC";

echo “
”;
echo “
”;

[/php]

See how much easy that becomes to maintain the code?

#5

Whoa, that was truly a much smarter and tidier way to clean my mess up. however i get no result but this:
Notice: Undefined index: search in /Applications/XAMPP/xamppfiles/htdocs/xampp/kashkool/search.php on line 94

Notice: Undefined index: cat in /Applications/XAMPP/xamppfiles/htdocs/xampp/kashkool/search.php on line 95

Notice: Undefined index: city in /Applications/XAMPP/xamppfiles/htdocs/xampp/kashkool/search.php on line 96

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘0’ at line 1

Any idea?

[php]




"; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } ?>[/php]
Search: Category: All! <?php 
                    $sql = "SELECT * FROM category";
                    $result = mysql_query($sql);
                    while($row = mysql_fetch_array($result)){ 
                        echo "<option value=\"" . $row["catID"] . "\">" . $row["catName"] . "</option>\n";
                    }
                    $sql="";
                    ?>
    </select></td>
    <th align="right">City:</th><br />
    <td><select name="city">
        <option value="0">Whole country</option>
                    <?php

                    $sql = "SELECT * FROM city";
                    $result = mysql_query($sql); 
                    while($row = mysql_fetch_array($result)){ 
                        echo "<option value=\"" . $row["cityID"] . "\">" . $row["cityName"] . "</option>\n";
                    }
                    $sql="";
                    ?>
<?php $search = $_GET["search"]; $cat = $_GET["cat"]; $city = $_GET["city"]; $sql = "SELECT * FROM tblad, city, category WHERE tblad.catID=category.catID AND tblad.cityID = city.cityID"; if ($search!="") { $sql += " Object LIKE '%{$search}%' "; } if ($cat!="") { $sql += " tblad.catID={$cat} "; } if ($city !="") { $sql += " city.cityID={$city} "; } $sql += " ORDER BY DateTime DESC"; echo "
"; echo "
"; //echo $sql; $result = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_array($result)) { echo "
" . $row["Object"] . "" . "" . $row["catName"] . "" . $row["Price"] . ":-" . "" . $row["DateTime"] . "" . $row["cityID"] . "
#6

Yeah, the SQL is just missing the AND statements, I added them below.

Also I changed how you assign the variables, I think that’s where your notices are coming from.

[php]$search = !isset($_GET[search’]) ? ‘’ : $_GET[‘value’];[/php]

[php]









"; echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } ?>[/php]
Search: Category:
All!
<?php 
                     $sql = "SELECT * FROM category";
                     $result = mysql_query($sql);
                     while($row = mysql_fetch_array($result)){ 
                         echo "<option value=\"" . $row["catID"] . "\">" . $row["catName"] . "</option>\n";
                     }
                     $sql="";
                     ?>
     </select></td>
     <th align="right">City:</th><br />
     <td><select name="city">
         <option value="0">Whole country</option>
                     <?php
 
                     $sql = "SELECT * FROM city";
                     $result = mysql_query($sql); 
                     while($row = mysql_fetch_array($result)){ 
                         echo "<option value=\"" . $row["cityID"] . "\">" . $row["cityName"] . "</option>\n";
                     }
                     $sql="";
                     ?>
<?php $search = !isset($_GET[search']) ? '' : $_GET['value']; $cat = !isset($_GET[cat']) ? '' : $_GET['cat']; $city = !isset($_GET[city']) ? '' : $_GET['city']; $sql = "SELECT * FROM tblad, city, category WHERE tblad.catID=category.catID AND tblad.cityID = city.cityID"; if ($search!="") { $sql += " and Object LIKE '%{$search}%' "; } if ($cat!="") { $sql += " and tblad.catID={$cat} "; } if ($city !="") { $sql += " and city.cityID={$city} "; } $sql += " ORDER BY DateTime DESC"; echo "
"; echo "
"; //echo $sql; $result = mysql_query($sql) or die(mysql_error()); while($row = mysql_fetch_array($result)) { echo "
" . $row["Object"] . "" . "" . $row["catName"] . "" . $row["Price"] . ":-" . "" . $row["DateTime"] . "" . $row["cityID"] . "
#7
#8

My mistake replace it with this.

[php] $search = !isset($_GET[‘search’]) ? ‘’ : $_GET[‘search’];
$cat = !isset($_GET[‘cat’]) ? ‘’ : $_GET[‘cat’];
$city = !isset($_GET[‘city’]) ? ‘’ : $_GET[‘city’];
[/php]

then after this

[php] $sql += " ORDER BY DateTime DESC";
[/php]

add this

[php]echo $sql [/php]

and show me the query.

#9

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘0’ at line 1

#10

No luck ._.
[php] $result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {

echo “

”;
echo “ ” . $row[“Object”] . “” . “”;
echo “” . $row[“catName”] . “”;
echo “” . $row[“Price”] . “:-” . “”;
echo “” . $row[“DateTime”] . “”;
echo “” . $row[“cityID”] . “”;
echo “”;
}[/php]
#11

change:

[php] echo $sql [/php]

to:

[php]echo $sql;[/php]

Then post the entire code again…

#12

Already have!

Here goes search.php

[php]

<?php include("connect.php"); ?> website 

<br />
<br />



”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
}
?>
Search: Category: All! <?php 
                      $sql = "SELECT * FROM category";
                      $result = mysql_query($sql);
                      while($row = mysql_fetch_array($result)){ 
                          echo "<option value=\"" . $row["catID"] . "\">" . $row["catName"] . "</option>\n";
                      }
                      $sql="";
                      ?>
      </select></td>
      <th align="right">City:</th><br />
      <td><select name="city">
          <option value="0">Whole country</option>
                      <?php
  
                      $sql = "SELECT * FROM city";
                      $result = mysql_query($sql); 
                      while($row = mysql_fetch_array($result)){ 
                          echo "<option value=\"" . $row["cityID"] . "\">" . $row["cityName"] . "</option>\n";
                      }
                      $sql="";
                      ?>
    </select></td>
    <td align="right"></td>
    <td>   <input type="submit" value="Find!" name="Sok" /></td>
</form></tr></table>

<table class="borderall">
<tr>
<th align="center" width="200" class="borderright">Object</th>
<th align="center" width="100" class="borderright">Category</th>
<th align="center" width="40" class="borderright">Price</th>
<th align="center" width="100" class="borderright">Date</th>
<th align="center" width="100">City</th>
</tr>


<?php
      
	$search =  !isset($_GET["search"]) ? '' : $_GET['search'];
   $cat = !isset($_GET["cat"]) ? '' : $_GET['cat']; 
   $city = !isset($_GET["city"]) ? '' : $_GET['city']; 
    
    $sql = "SELECT * FROM tblad, city, category WHERE tblad.catID=category.catID AND		    tblad.cityID = city.cityID";
                  
       if ($search!="")
         {
         $sql += " AND Object LIKE '%{$search}%'  ";
         }
   
      if ($cat!="")
        {
         $sql += " AND tblad.catID={$cat} ";
         }
   
      if ($city !="")
        {
         $sql += " AND city.cityID={$city} ";
         }
   
    $sql += " ORDER BY DateTime DESC";

echo $sql;
echo “
”;

   //echo $sql;
  $result = mysql_query($sql) or die(mysql_error()); 
  while($row = mysql_fetch_array($result)) {

echo “
” . $row[“Object”] . “” . “” . $row[“catName”] . “” . $row[“Price”] . “:-” . “” . $row[“DateTime”] . “” . $row[“cityID”] . “
[/php]
#13

Try the below, I’m getting my languages mixed up.

[php]<?php
include(“connect.php”);
?>

website 

  <br />
  <br />



”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
echo “”;
}
?>
Search: Category: All! <?php 
                        $sql = "SELECT * FROM category";
                        $result = mysql_query($sql);
                        while($row = mysql_fetch_array($result)){ 
                            echo "<option value=\"" . $row["catID"] . "\">" . $row["catName"] . "</option>\n";
                        }
                        $sql="";
                        ?>
        </select></td>
        <th align="right">City:</th><br />
        <td><select name="city">
            <option value="0">Whole country</option>
                        <?php
    
                        $sql = "SELECT * FROM city";
                        $result = mysql_query($sql); 
                        while($row = mysql_fetch_array($result)){ 
                            echo "<option value=\"" . $row["cityID"] . "\">" . $row["cityName"] . "</option>\n";
                        }
                        $sql="";
                        ?>
      </select></td>
      <td align="right"></td>
      <td>   <input type="submit" value="Find!" name="Sok" /></td>
  </form></tr></table>
  
  <table class="borderall">
  <tr>
  <th align="center" width="200" class="borderright">Object</th>
  <th align="center" width="100" class="borderright">Category</th>
  <th align="center" width="40" class="borderright">Price</th>
  <th align="center" width="100" class="borderright">Date</th>
  <th align="center" width="100">City</th>
  </tr>
  
  
  <?php
        
	$search =  !isset($_GET["search"]) ? '' : $_GET['search'];
     $cat = !isset($_GET["cat"]) ? '' : $_GET['cat']; 
     $city = !isset($_GET["city"]) ? '' : $_GET['city']; 
      
      $sql = "SELECT * FROM tblad, city, category WHERE tblad.catID=category.catID AND		    tblad.cityID = city.cityID";
                    
        if ($search!="")
          {
          $sql .= " AND Object LIKE '%{$search}%'  ";
          }
    
       if ($cat!="")
         {
          $sql .= " AND tblad.catID={$cat} ";
          }
    
       if ($city !="")
         {
          $sql .= " AND city.cityID={$city} ";
          }
    
     $sql .= " ORDER BY DateTime DESC";

echo $sql;
echo “
”;

    //echo $sql;
   $result = mysql_query($sql) or die(mysql_error()); 
   while($row = mysql_fetch_array($result)) {

echo “
” . $row[“Object”] . “” . “” . $row[“catName”] . “” . $row[“Price”] . “:-” . “” . $row[“DateTime”] . “” . $row[“cityID”] . “
[/php]
#14

This worked perfect! Except for one thing. I wanted my search to work like this:
If you’d fill in for example “Car” in the searchfield and leave the category and city in default-value you get the result that shows all car ads from all categories and all cities.

If you leave them all blank and search a list of all articles will show.

If you’d specify only category you’d get all the results for the category you selected, such as Electronics.

If you’d specify only city you’d get all the results for your choice, such as Paris.

If you’d specify only category and city (Electronics and Paris) and leave search field blank, you’d get the results that shows all ads in those two choices.

If you’d fill in the search field and category (or city) the result will show all ads (input keyword in search field) within the category (or city)

#15

And what is it doing? It should be doing everything as you described.

#16

The result is all blank when i press submit, no matter what my specified choices are,

by the way - would it be difficult to build a thumbnail from the respective article at the search result field? ;o

#17

Change:

[php] echo $sql;[/php]

to

[php] echo $sql;
exit;[/php]

and tell me what it spits out.

#18

Still nothing!

[php]<?php

		$search =  !isset($_GET["search"]) ? '' : $_GET['search'];
       $cat = !isset($_GET["cat"]) ? '' : $_GET['cat']; 
       $city = !isset($_GET["city"]) ? '' : $_GET['city']; 
        
        $sql = "SELECT * FROM tblad, city, category WHERE tblad.catID=category.catID AND		    tblad.cityID = city.cityID";
                      
          if ($search!="")
           {
           $sql .= " AND Object LIKE '%{$search}%'  ";
           }
     
        if ($cat!="")
          {
           $sql .= " AND tblad.catID={$cat} ";
           }
     
        if ($city !="")
          {
           $sql .= " AND city.cityID={$city} ";
           }
     
      $sql .= " ORDER BY DateTime DESC";

echo “
”;

    $result = mysql_query($sql) or die(mysql_error()); 
    while($row = mysql_fetch_array($result)) {

echo “

”;
/* <> echo “ ” . $row[“Picture”] . “” . “”;*/
echo “ ” . $row[“Object”] . “” . “”;
echo “” . $row[“catID”] . “”;
echo “” . $row[“Price”] . “:-” . “”;
echo “” . $row[“DateTime”] . “”;
echo “” . $row[“cityName”] . “”;
echo “”;
}

//echo $sql;
exit();[/php]

#19

I need the

[php]echo $sql;
exit;[/php]

right after this line

[php]$sql .= " ORDER BY DateTime DESC"; [/php]

I want to see the SQL Statement that is going to execute based on your selection. If nothing displays, then the error is someplace before that statement.

#20

Aha, interesting!
When I’ve put the 2 lines as you recommended and try to search for something i get the following message:

SELECT * FROM tblad, city, category WHERE tblad.catID=category.catID AND tblad.cityID = city.cityID AND Object LIKE ‘%Find!%’ AND tblad.catID=0 AND city.cityID=0 ORDER BY DateTime DESC

This kinda means that it’s acting according to plan, but what is being faulty is the Showing-part of the php code. Or am I entirely wrong? :-\

Sponsor our Newsletter | Privacy Policy | Terms of Service