Dropdown search from MySQL Database help

[mlucianpavel1]

Hello.
I'm trying to make a dropdown script to work and I'm stuck to a point where I feel the answer is simple ,but I simply can't see it .
The script should display data from the MySQL Database when I choose the last option but I don't know how to do it correctly, if someone wants to help me I would be grateful.

what i have :

MySQL Database :

category_1 
--cat_id
--category

subcategory
--subcat_id     
--cat_id    
--subcategory

subcategory2
--subcat_id     
--subcat2

rezultate
--subcat_id     
--articol   
--linieprodus   
--tensiune  
--acumulator    
--curentelectric

pages:

db.inc (for connection to database )
index.php ( code on pastebin.com )

Pastebin

The result with what I have...
When i choose the last option from the dropdown is showing only correctly " subcat2" from database .
.

What I want to do and I'm stuck

When i choose the last option from the dropdown I want to show a table on the same page with :

--articol ( link to an article ) | --linieprodus | --tensiune | --acumulator |

from the database "rezultate"

question

How do I make the connection between " rezultate " , "subcategory2" and "index.php" to show the results that I choose from the last option from dropdown ?

If it is a easy way of doing this without my messy code , I'm open for suggestions
Hope I explained good what I'm trying to do , thanks in advance for answers.
English is not my native language , so please excuse my spelling mistakes.
Thanks.

Lucian

[jkon]

If I understand what you are doing, you are trying to create a select input with categories that when the selected option change a HTML table were the “rezultates” are shown. Of course if you want to do it dynamically without page form submission you should use AJAX.

To be honest this is something very simple that you could do in minutes using a framework. I am not writing scripts, but if you continue to face problems with that , tell me to help also in plain script.

I have some observations although that they might help you in future. English aren’t also my native language, but I strongly believe that keeping table names, column names, object’s properties names and Classes in English is a big help.

From a db stracture I understand that a “category_1” has some “subcategory” and each of them has some “subcategory2”, this releshion is best described in a self referential table, meaning that category_1 , subcategory and subcategory2 are all categories so they could be in the same table with an id and a parent_id more over if you want to lock categories in order to have only subcategories or “rezultates” you could have a column defining the type of it (e.g. 0 category with subcategories , 1 category with “rezultates”). That way your database will be cleaner and your PHP code might make more sense.

When you have a table there is no need to name the id after the table (e.g. cat_id could be just id, either way when you are going to need it for a join you should also have the table where it is). In the same way, when a table has a reference id (e.g. the table subcategory2) could be named subcategory_id. These are just naming conventions but believe be they help.

Inside your script you query database with just mysql_query using variable from Get without even mysql_real_escape_string. Of course that way your script is an excellent gateway for attacks. PHP has a great interface for accessing a database; PDO . With PDO you can also have prepared statements without warring about security in that field.

Hope I helped a little; these might not be the solution to the issue you mentioned, but are some topics you will gain if you think about them.

[mlucianpavel1]

Hello jkon ,
thanks for your reply I will take what you say in consideration.
You said

To be honest this is something very simple that you could do in minutes using a framework

with what framework can I do that ? I know that my code and database is not clean , but that was the only one found , and does in most part what I want .

I have some observations although that they might help you in future. English aren’t also my native language, but I strongly believe that keeping table names, column names, object’s properties names and Classes in English is a big help.

I'm really sorry about that , I was tired and forgot to change back the example in english.

From a db stracture I understand that a “category_1” has some “subcategory” and each of them has some “subcategory2”, this releshion is best described in a self referential table

I found an database example on what you said ,some time ago , but didn't know how to show it in a table. About the same what I ask here...
it was something like this

category_1
--cat_id
--category
--subcat_id
--cat_id
--subcategory
--subcat_id
--subcat2
--subcat_id
--article
--product
--tension
--battery
--electric power

Hope I helped a little; these might not be the solution to the issue you mentioned, but are some topics you will gain if you think about them.

Again thanks for your reply , and as I said I know my code is not the best , and I'm open to suggestions.

[jkon]

Don’t be sorry my friend, if this is one of your steps you are going well, keep walking ;))) . Daniweb is an excellent community to share knowledge or opinions and that what we are doing. Let me please ask few things before trying to post code as answer. What is this project about? Can you use frameworks on it? Are you really familiar with OOP or it must be strict procedural scripting?

Also let me be sure that I have understood the business logic, correct me If anything of those is wrong or if anything is missing. We have products of a certain type (if we know that type its best to name the table after it). Each of them has an id , a link («articol ( link to an article )» is that outside your project ? or it is has something to do with another table?) It also has name (‘linieprodus’) , tension , battery and electric power. What are the type of those columns (are they strings with a specific max length , are some of them numeric?)

Also each product of that type belongs in a category. Can a category that has subcategories have also products ? And of course how the data that we are going to display this way are entered in database? If it is what I understand a category has an id, a parent id , a type (if we restrict categories to be only with products or subcategories e.g. 0 with subcategories , 1 with products) and a status would be nice (maybe you want to inactivate a category for some reason , e.g. 1 by default active , 0 inactive)

I continue with I have understood and you correct me. What we want is to have a page where all categories with products are in a select ( of course the path from the parent category to them will be the text of the option and the id the value) and when a user selects a category all the products of this category will be shown in a table. Do we want this to happen without the user reloads any page (AJAX) or we should use normal post?

(If we were using a framework we could redirect the user to a page whose URL would had the path of the category we choose (from parent to this one) in that case we must add one field to our categories table URL that it will be the URL segment, we can do it also in procedural scripting programming but I can’t thing how tough would be).

[mlucianpavel1]

Thanks , the keep walking part it makes me think of Johnnie Walker:))
Ok , I will try to answer your questions as well as I can.

What is this project about?

I'm trying to make an product finder / search engine for my car parts / batteries sites , some examples of product finders can be found in the links below
Link 1
Link 2
Link 3
Link 4
Link 5

Can you use frameworks on it?

If by frameworks you are saying CodeIgniter , CakePHP ,Yii . I dont know how to build sites in frameworks, if you are saying framework (CMS) as in , Wordpress , Joomla , OpenCart ,etc , then yes .

Are you really familiar with OOP

I'm not familiar with OOP

We have products of a certain type (if we know that type its best to name the table after it). Each of them has an id , a link («articol ( link to an article )» is that outside your project ? or it is has something to do with another table?)

This is the best example I can give on how it should look , I dont want something fancy , I just want it to work :)

Also each product of that type belongs in a category. Can a category that has subcategories have also products ? And of course how the data that we are going to display this way are entered in database?

I thought about it but I said not to complicate. The data , I was thinking about an admin page, but first I want to make it work . I

h would be).

can enter the data through phpMYAdmin until I get to the admin page

maybe you want to inactivate a category for some reason

wow , I never thought about it , but you got a good point , for example I run out of products on on category and dont want to display it anymore , can that be done ???

Do we want this to happen without the user reloads any page (AJAX) or we should use normal post?

If I understood you correctly , when the user makes the choice from the dropdown the data will display without reloding the page (AJAX) and from a botton ( submit ) normal posting? I like without reloading the page , but if is to hard ,normal posting is also good.

If we were using a framework we could redirect the user to a page whose URL would had the path of the category we choose (from parent to this one) in that case we must add one field to our categories table URL that it will be the URL segment, we can do it also in procedural scripting programming but I can’t thing how tough would be

I really dont know what to answer , but I will try.
When the script is done and working , I can implement it in other scripts , even if I have to make the displayed data two times , one in the primary script ( Wordpress , Joomla , OpenCart ,etc ) and the second in this script. Even when I think about it ,I dont know what to do , what will you recomand ?

Again , I dont know how to thank you , looking for examples everywhere and didn't found anything to work . If I was a good programmer , probably didn't think it so hard, well I'm not a good programmer so I ask for help, at least I learn something from this.

[jkon]

I had many years to write scripting without OOP and a framework. To be honest it was tougher than I first imagine, and it is a good example why OOP is in fact easier.
This is just an example; I wouldn’t recommend anyone to go this way, since writing PHP without OOP will end in a script mess. You can modify this to your needs , and change the view or data.

We have two tables, categories and products, here are the create table of them

CREATE TABLE `categories` (
 `ID` int(10) NOT NULL AUTO_INCREMENT,
 `PARENT_ID` int(10) NOT NULL,
 `TYPE` smallint(1) NOT NULL COMMENT '0 Category with subcategories | 1 Category with products',
 `TITLE` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `STATUS` smallint(1) NOT NULL DEFAULT '1' COMMENT '0 Inactive | 1 Active',
 PRIMARY KEY (`ID`)
) ENGINE=MyISAM

When a gategory has parent id 0 than it is a top category

CREATE TABLE `products` (
 `ID` int(10) NOT NULL AUTO_INCREMENT,
 `CATEGORY_ID` int(10) NOT NULL,
 `ARTICLE_LINK` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `PRODUCT_LINE` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 `VOLTAGE` int(4) NOT NULL,
 `BATTERY` int(4) NOT NULL,
 `ELECTRIC_POWER` int(4) NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=MyISAM

And here is the test.php

<?php
 session_start();
 // Change these with your own database,user and password
 $database = "test_cars";
 $dbUser = "testcarsuser";
 $dbPassword = "testcarspassword";

 $db = new PDO("mysql:dbname=".$database.";host=localhost",$dbUser,$dbPassword);

 //There is no reason to get categories each time, so we will use visit scope caching through session 
 //(although if we were using a framework this could be done by application scope caching) 
 if(isset($_SESSION["CATSARRAY"]) && isset($_SESSION["CATSINPUT"]))
 {
   $catsArray = $_SESSION["CATSARRAY"];
   $catsInput= $_SESSION["CATSINPUT"];
 }
 else 
 {
   // $catsArray We will create an array with the categories rows. It's first key will be the id of the category 
   // second key will be the column name of each row
   // $prodCatsIds We will create also an array with all the ids of the categories that have products 

   $catsArray = array(); 
   $catsInput = "";
   $prodCatsIds = array(); 

   $statement = $db->prepare("SELECT * FROM categories WHERE STATUS = 1");
   $statement->execute(array());
   $r = $statement->fetchAll(PDO::FETCH_ASSOC);

   // Now $r will be an array with the first key being the number of the row returned and the second the 
   // name of the column 
   if(count($r) > 0)
   {
     foreach($r as $row)
     {
       $catsArray[$row["ID"]] = $row; 
       // If it is a products category we will keep it's id in $prodCatsIds to use it later
       if($row["TYPE"] == 1)
       {
         $prodCatsIds[] = $row["ID"]; 
       }
     }
   }

   $_SESSION["CATSARRAY"] = $catsArray; 

   // Using $prodCatsIds we will create an array whose key will be the path to the product category 
   // (from parent to child) and the value will be the id of that category
   if(count($prodCatsIds) > 0)
   {
     foreach($prodCatsIds as $id)
     {
       $selectArray[getCatPath($id)] = $id; 
     }
     // Let's short the selectArray by keys (path's names)
     ksort($selectArray);
   }

   //It's time to create our select input for the form 
   if(count($selectArray) > 0)
   {
     $catsInput = "<select name='category' id='category' onchange='javascript:categorySelected()'>";
     $catsInput .= "<option value='none'>Select Category</option>";
     foreach($selectArray as $value => $key)
     {
       $catsInput .= "<option value='$key'>$value</option>";
     }
     $catsInput .= "</select>";
   } 

   $_SESSION["CATSINPUT"] = $catsInput; 
 }

 // A function that will work recursively to generate the 'path' to the category
 function getCatPath($id)
 {
   $re = "";
   global $catsArray;
   $row = $catsArray[$id];
   if($row["PARENT_ID"] != 0)
   {
     $re .= getCatPath($row["PARENT_ID"])." &raquo; ";
   }
   $re .= $row["TITLE"];
   return $re; 
 }

 // We will use the same scipt to post by AJAX the category id and retrieve the table with products
 // Of course could be better to return an array with data and not the html table but that would 
 // make this example more complicated. Any security check that would validate that it is a post 
 // through AJAX call and that the post came out of the same script is needed.  
 if(isset($_POST["categoryId"]))
 {
   $id = $_POST["categoryId"]; 
   $statement = $db->prepare("SELECT * FROM products WHERE CATEGORY_ID = ?");
   $statement->execute(array($id));
   $r = $statement->fetchAll(PDO::FETCH_ASSOC);

   $re = getCatPath($id) ." Products<br/>"; 
   if(count($r) == 0)
   {
     $re .= "There aren't any product yet in that category";
   }
   else 
   {
     $re .= "<table><tr><th>Article Link</th><th>Product Line</th><th>Voltage</th><th>Battery</th><th>Electric Power</th></tr>";
     foreach ($r as $row)
     {
       $re .= "<tr><td><a href='".$row["ARTICLE_LINK"]."' target='_blank'>".$row["ARTICLE_LINK"]."</a></td>
       <td>".$row["PRODUCT_LINE"]."</td><td>".$row["VOLTAGE"]."</td><td>".$row["BATTERY"]."</td>
       <td>".$row["ELECTRIC_POWER"]."</td></tr>";
     }
     $re .= "</table>";
   }

   $result = array("HTML" => $re); 
   header("Content-Type: application/json; charset=utf-8");
   echo json_encode($result);
   exit;
 }

 // LETS MOVE TO THE NORMAL PAGE OUTPUT
 header('Content-Type: text/html; charset=utf-8');
?>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Example</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript">
// This will happen every time the user change the selected category
function categorySelected()
{
    catsEl = document.getElementById("category");
    id = catsEl.options[catsEl.selectedIndex].value;
    if(id != "none")
    {
        document.getElementById("result").innerHTML = "loading...";
        params = "categoryId="+id;
        http = window.XMLHttpRequest ? new XMLHttpRequest() : new ActiveXObject("Microsoft.XMLHTTP"); 
        http.open("POST", document.URL, true);
        http.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        http.setRequestHeader("X-Requested-With","XMLHttpRequest");
        http.setRequestHeader("Content-length", params.length);
        http.setRequestHeader("Connection", "close");

        http.onreadystatechange = function() 
        {
            if(http.readyState == 4)
            { 
                if(http.status == 200)
                {
                    response = http.responseText;
                    r = eval("(" + response + ")");
                    document.getElementById("result").innerHTML = r["HTML"];    
                }
                else 
                {
                    document.getElementById("result").innerHTML = "Sorry this service is currently unavailable";    
                }   
            }
        } 
        http.send(params);
    }
}
</script>
</head>
<body>
<?=$catsInput?>
<br/>
<br/>
<br/>
<div id="result"></div>
</body>
</html>

I am sure there could be 1000 other ways and that even with scripting could be done better, but don’t take this answer and just use it. Take a moment to think that maybe it is the time to learn OOP and a framework. Although this is working it certainly is not the solution…

[mlucianpavel1]

After writing a long reply with what I tried my browser crushed and lost what I wrote you , so here is the short version of that lost in space reply :D

First Thank you for the help.
The script is awesome , but I got some problems with it.

PDO error , fixed that by adding

extension=pdo.so
extension=pdo_mysql.so

in the php.ini
One error, Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent in line 2 , after I searched on google found something , removing any spaces before session_start(); in test.php , after that I placed test categories and two products for testing , category => subcategory => subcategory and product 1 and product 2 got an error , Fatal error: Allowed memory size of 134217728 bytes exahausted (tried to allocate 523800 bytes) in /home/..../public_html/..../4/test.php on line 83 played around with that line , and the dropdown showed categories and subcategories in one line , if I choose that , the products appeared as it should. Tried again from a friends PC and a phone , blank pages.
After every thing I did I flushed my dns , changed the IP, delete cookies and history from my browser.

Again , I don't know how to thank you.
I'm thinking that the answer is somewere there and I don't see it.
Can you please help me further?

[jkon]

How did you change that line (83) ? I will not stand there because you say that now it is working (although you shouldn’t have any session_start problem or memory issues, of course I have tried this answer before sending to you).

So you are saying that know everything works great from your PC but when you try it from other PC’s you see a blank page? Where is your server? (and what is it ?)

Also can you give a example of the data you entered (categories and products), maybe first problem has to do with data.

[mlucianpavel1]

hello friend , nevermind that line (83) it was a space in the code , I don't know why the spaces give me a hard time ... the database is like this

-- phpMyAdmin SQL Dump
    -- version 3.4.10.1
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Aug 18, 2012 at 09:56 PM
    -- Server version: 5.5.24
    -- PHP Version: 5.2.6

    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    SET time_zone = "+00:00";


    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;

    --
    -- Database: `lucky_bateries`
    --

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

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

    CREATE TABLE IF NOT EXISTS `categories` (
      `ID` int(10) NOT NULL AUTO_INCREMENT,
      `PARENT_ID` int(10) NOT NULL,
      `TYPE` smallint(1) NOT NULL COMMENT '0 Category with subcategories | 1 Category with products',
      `TITLE` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `STATUS` smallint(1) NOT NULL DEFAULT '1' COMMENT '0 Inactive | 1 Active',
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

    --
    -- Dumping data for table `categories`
    --

    INSERT INTO `categories` (`ID`, `PARENT_ID`, `TYPE`, `TITLE`, `STATUS`) VALUES
    (1, 0, 0, 'category 1', 1),
    (2, 1, 1, 'subcategory 1', 1),
    (4, 3, 1, 'subcategory 2', 1),
    (3, 0, 0, 'category 2', 1);

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

    --
    -- Table structure for table `products`
    --

    CREATE TABLE IF NOT EXISTS `products` (
      `ID` int(10) NOT NULL AUTO_INCREMENT,
      `CATEGORY_ID` int(10) NOT NULL,
      `ARTICLE_LINK` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `PRODUCT_LINE` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `VOLTAGE` int(4) NOT NULL,
      `BATTERY` int(4) NOT NULL,
      `ELECTRIC_POWER` int(4) NOT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

    --
    -- Dumping data for table `products`
    --

    INSERT INTO `products` (`ID`, `CATEGORY_ID`, `ARTICLE_LINK`, `PRODUCT_LINE`, `VOLTAGE`, `BATTERY`, `ELECTRIC_POWER`) VALUES
    (1, 2, 'google.com', 'product 1', 50, 600, 50),
    (2, 4, 'google.us', 'product 2', 54, 400, 79);

    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

and a demo link for you to see if it is right... http://test.zzzz.ro/1/demo.php
Now I don't have any errors , but when the category is selected it does not appear like this , first dropdown , category ,second dropdown , subcategory , third dropdown , subcategory 2... Thats because my database and how I entered the data ??
Can you give me an example of database ?
Thanks.

[jkon]

Hello mlucian,
Yes the example you gave works very well (maybe more products than one in a category would help demonstrating this more, but it is also ok as it is).

There is one question that now I feel that I should have asked from the beginning. Are those categories / subcategories that you are referring of the same type? If are this is the way to go , with one select with all the categories that have products (and the path to them). If they aren’t it is a whole different data model approach. For example a subcategory (let’s say secondary type specification) can have two parent categories?

If it hasn’t (if…) and all you want is to present the data with steps (first select top category , then second , and then third and then the products) you simple have to have 3 Ajax calls. The first one will bring a select with subcategories from a top category, the second one the subcategories of a subcategory and the third one the products. But I am not so sure that this is your case (remember that as we create more functionality to it so harder is to maintain it without OOP)

Back to your data. You are saying about a tree that goes 3 levels up (or a snake with three curves as I like it to refer) but in your data you have only 2 levels , e.g. category 1 » subcategory 1 and category 2 » subcategory 2) what of those is true , data or what you are explaining ?

The trouble you have about spaces could have to do with your server and how buffering on should set on it, but it might be something simple (for example do you encode your PHP files in UTF-8 without BOM ? ) . The main key to my previous answer was not to take a code and plug it in your site, but to understand how a thing like that can work. And as I can see it is working great. Explain a bit more what categories / subcategories are and how you have thought it should work and I am sure that I (an more people here) will help about it.

[mlucianpavel1]

hello jkon , yes the parent category can have more subcategories , like in this example http://cautare.baterie-auto.eu/4/index.php , but the data have to show how it it shown in your example , can that be done ? and if it is with OOP it does not matter . thanks in advance . can be any language ( php , oop , .net , as long as it work , it is ok :) )
thanks again for your support , you are great !