SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Sql array query help

    Hi guys,

    I have the following db structure:

    Code:
     `product_id` varchar(20) NOT NULL default '',
      `product_name` varchar(100) NOT NULL default '',
      `keyword1` varchar(100) NOT NULL default '',
      `keyword2` varchar(100) NOT NULL default '',
      `keyword3` varchar(100) NOT NULL default '',
      `keyword4` varchar(100) NOT NULL default '',
      `keyword5` varchar(100) NOT NULL default '',
      `keyword6` varchar(100) NOT NULL default '',
      `keyword7` varchar(100) NOT NULL default '',
      `keyword8` varchar(100) NOT NULL default '',
      `keyword9` varchar(100) NOT NULL default '',
      `keyword10` varchar(100) NOT NULL default '',
      `keyword11` varchar(100) NOT NULL default '',
      `keyword12` varchar(100) NOT NULL default '',
      `keyword13` varchar(100) NOT NULL default '',
      `keyword14` varchar(100) NOT NULL default '',
      `keyword15` varchar(100) NOT NULL default '',
      `keyword16` varchar(100) NOT NULL default '',
      `keyword17` varchar(100) NOT NULL default '',
      `keyword18` varchar(100) NOT NULL default '',
      `keyword19` varchar(100) NOT NULL default '',
      `keyword20` varchar(100) NOT NULL default '',
      PRIMARY KEY  (`product_id`)
    I want to perform a query to echo out the contents of each the 'keyword 1-20' columns if there is data in it. (some entries dont have 20 keywords)

    How would I do it like so:

    apples, bananas, pears

  2. #2
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Some database reworking would probably be better, a simple query you might try though is

    Code PHP:
    $query = "SELECT * FROM table";
    // ... crud to execute query
     
     
    $result_set = $database_object->fetchrow_array();
    $product_id = array_shift($result_set);
    $product_name = array_shift($result_set);
     
    $echo_statement = join(', ', $result_set);
    $echo_statement = str_replace(', ,', ', ', $echo_statement);
     
    echo $echo_statement

    There are probably better ways to do this.

    A more practical solution would be

    Code MySQL:
    CREATE TABLE product_keyword(
       `id` INT NOT NULL auto increment,
       `product_id` INT NOT NULL,
       `keyword` varchar(100) NOT NULL default '',
       PRIMARY KEY(`id`),
       key `product_id` (`product_id`),
      UNIQUE KEY `product_id_keyword`(`keyword`, `product_id`)
    );

    This table has two nice things, a I've converted product_id to an int. I'm assuming that it's a foreign key in your table. If it is it should be the exact type as the primary key in the referenced table thus mysql will use indexes and the result will be much faster. Also now you can do the following queries in mysql

    Code MySQL:
    SELECT group_concat(keyword) FROM product_keyword group by product_id;

    This will return apple, banana, carrot, ... for each product that you have

    Code MySQL:
    SELECT keyword FROM product_keyword WHERE product_id = 1;

    This will return
    apple
    banana
    carrot
    ....

    Another solution given your previously setup table are as follows
    Code MySQL:
    SELECT 
    CONCAT( 
    	CAST(`keyword1` AS char),
    	CAST(`keyword2` AS char),
    	CAST(`keyword3` AS char),
    	CAST(`keyword4` AS char),
    	CAST(`keyword5` AS char),
    	CAST(`keyword6` AS char),
    	CAST(`keyword7` AS char),
    	CAST(`keyword8` AS char),
    	CAST(`keyword9` AS char),
    	CAST(`keyword10` AS char),
    	CAST(`keyword11` AS char),
    	CAST(`keyword12` AS char),
    	CAST(`keyword13` AS char),
    	CAST(`keyword14` AS char),
    	CAST(`keyword15` AS char),
    	CAST(`keyword16` AS char),
    	CAST(`keyword17` AS char),
    	CAST(`keyword18` AS char),
    	CAST(`keyword19` AS char)
    ) 
    FROM table_1

  3. #3
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the reply & very informative post

    I'm assuming that it's a foreign key in your table
    Sorry not sure what you mean here?

    This will return apple, banana, carrot, ... for each product that you have
    how would i write the echo code for that?

  4. #4
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I have taken your advice and decided on database normalisation, changing the way I setup my original tables.

    I setup this one:
    PHP Code:
    CREATE TABLE Product_keywords(
       `
    idINT NOT NULL auto_increment,
       `
    product_idINT NOT NULL,
       `
    keywordVARCHAR(100NOT NULL DEFAULT '',
       
    PRIMARY KEY(`id`),
       
    key `product_id` (`product_id`),
      
    UNIQUE KEY `product_id_keyword`(`keyword`, `product_id`)

    I had originally written a php script to import csv data into the database (list of product id's and their keywords (1-20 columns)

    PHP Code:

    # Delete the table 

    $result mysql_query("DROP TABLE IF EXISTS Search_Keywords"
        or die(
    mysql_error()); 

    # check for errors
        
    if (!$result) { echo("ERROR: " mysql_error() . "\n\n");    }
        
            else     {
    # if successful
        
                
    }


    #######################################


    # Create the table

    $sql "CREATE TABLE Search_Keywords(

        product_id varchar(20) NOT NULL default '',
         product_name varchar(100) NOT NULL default '',
        keyword1 varchar(100) NOT NULL default '',
        keyword2 varchar(100) NOT NULL default '',
        keyword3 varchar(100) NOT NULL default '',
        keyword4 varchar(100) NOT NULL default '',
        keyword5 varchar(100) NOT NULL default '',
        keyword6 varchar(100) NOT NULL default '',
        keyword7 varchar(100) NOT NULL default '',
        keyword8 varchar(100) NOT NULL default '',
        keyword9 varchar(100) NOT NULL default '',
        keyword10 varchar(100) NOT NULL default '',
        keyword11 varchar(100) NOT NULL default '',
        keyword12 varchar(100) NOT NULL default '',
        keyword13 varchar(100) NOT NULL default '',
        keyword14 varchar(100) NOT NULL default '',
        keyword15 varchar(100) NOT NULL default '',
        keyword16 varchar(100) NOT NULL default '',
        keyword17 varchar(100) NOT NULL default '',
        keyword18 varchar(100) NOT NULL default '',
        keyword19 varchar(100) NOT NULL default '',
        keyword20 varchar(100) NOT NULL default '',
      
        PRIMARY KEY(product_id))"
    ;

    $result MYSQL_QUERY($sql) or die
        (
    "Invalid create");



    #######################################


    # write new data to table

        
    $file_name $_FILES['userfile']['name'];
        
    $file_tmp  $_FILES['userfile']['tmp_name'];
        
    $handle fopen("$file_tmp""r");
        
        
        
    // skip the first row

    fgetcsv($handle1000",");

            while ((
    $data fgetcsv($handle1000",")) !== FALSE)
             {
            
            
             
    $import="INSERT into Search_Keywords(

    product_id,
    product_name,
    keyword1,
    keyword2,
    keyword3,
    keyword4,
    keyword5,
    keyword6,
    keyword7,
    keyword8,
    keyword9,
    keyword10,
    keyword11,
    keyword12,
    keyword13,
    keyword14,
    keyword15,
    keyword16,
    keyword17,
    keyword18,
    keyword19,
    keyword20
        ) values(


    '" 
    mysql_real_escape_string($data[0]) . "',
    '" 
    mysql_real_escape_string($data[1]) . "',
    '" 
    mysql_real_escape_string($data[2]) . "',
    '" 
    mysql_real_escape_string($data[3]) . "',
    '" 
    mysql_real_escape_string($data[4]) . "',
    '" 
    mysql_real_escape_string($data[5]) . "',
    '" 
    mysql_real_escape_string($data[6]) . "',
    '" 
    mysql_real_escape_string($data[7]) . "',
    '" 
    mysql_real_escape_string($data[8]) . "',
    '" 
    mysql_real_escape_string($data[9]) . "',
    '" 
    mysql_real_escape_string($data[10]) . "',
    '" 
    mysql_real_escape_string($data[11]) . "',
    '" 
    mysql_real_escape_string($data[12]) . "',
    '" 
    mysql_real_escape_string($data[13]) . "',
    '" 
    mysql_real_escape_string($data[14]) . "',
    '" 
    mysql_real_escape_string($data[15]) . "',
    '" 
    mysql_real_escape_string($data[16]) . "',
    '" 
    mysql_real_escape_string($data[17]) . "',
    '" 
    mysql_real_escape_string($data[18]) . "',
    '" 
    mysql_real_escape_string($data[19]) . "',
    '" 
    mysql_real_escape_string($data[20]) . "',
    '" 
    mysql_real_escape_string($data[21]) . "'

    )"
    ;
            
    mysql_query($import) or die(mysql_error());
             }

         
    fclose($handle);
         print 
    "<font class=menu><b>import complete!</b></font>"
    How would I rewrite this to add the data into the newly created db table that you suggested?


    My CSV looks like this:
    product_id | product_name | Keyword1 | Keyword2 etc... upto 20 keywords

  5. #5
    SitePoint Addict
    Join Date
    Jul 2007
    Location
    San Jose, California
    Posts
    355
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This should do it.....though there is some code missing for you to fill in, but the majority of it is there.

    PHP Code:
    <?php


    //Create Table for Products
    mysql_query("CREATE TABLE product(
                    `id` INT not null,
                    `name` VARCHAR(100)
                    PRIMARY KEY `id`
                )"
    );
                
    //Create Table for keywords    
    mysql_query("CREATE TABLE search_keyword(
                       `id` INT NOT NULL auto increment,
                       `product_id` INT NOT NULL,
                       `keyword` VARCHAR(100) NOT NULL DEFAULT '',
                       PRIMARY KEY(`id`),
                       key `product_id` (`product_id`),
                      UNIQUE KEY `product_id_keyword`(`keyword`, `product_id`)
                )"
    );

    fgetcsv($handle1000",");

    //Insert Queries
    //Used ignore because there is a possibly the same product has the same keyword 
    //twice, and these will give a mysql warning because of the unique key on keyword
    //and product_id there for, the INSERT IGNORE will not do the insert and will not
    //prompt the warning
    $insert_keyword "INSERT IGNORE INTO `search_keyword` VALUES (NULL, %d, '%s')";
    $insert_product "INSERT INTO `product` VALUES (%d, '%s')";
    while ((
    $data fgetcsv($handle1000",")) !== FALSE) {
        
    $product_id array_shift($data);
        
    $product_name array_shift($data);
        
        
    //Insert product into product table
        
    mysql_query(sprintf($insert_product
                            
    mysql_real_escape_string(intval($product_id)), 
                            
    mysql_real_escape_string($product_name
                    ));
                    
        
    //Loop through the keywords and insert onto database
        
    foreach($data as $keyword){
            if( 
    strlen(trim($keyword)) ){
                
    mysql_query(sprintf($insert_keyword
                                    
    mysql_real_escape_string(intval($product_id)), 
                                    
    mysql_real_escape_string($keyword
                            ));
            }
        }
    }

    //Run Query to get all products, and their keywords
    $result mysql_query("SELECT p.id, p.name, group_concat(keyword) FROM product p 
                            JOIN search_keyword sk ON sk.product_id = p.id
                            GROUP BY p.id"
    );
    while (list(
    $id$name$keywords) = mysql_fetch_row($result)) {
        print 
    "Product_id: $id, Name: $name, Keywords: $keywords\n";
    }

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the product_keywords table is the correct approach

    however, it should ~not~ have an auto_increment id

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    however, it should ~not~ have an auto_increment id
    Why not? What should the id be instead?

  8. #8
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by baileylo View Post
    This should do it.....though there is some code missing for you to fill in, but the majority of it is there.

    PHP Code:
    <?php


    //Create Table for Products
    mysql_query("CREATE TABLE product(
                    `id` INT not null,
                    `name` VARCHAR(100)
                    PRIMARY KEY `id`
                )"
    );
                
    //Create Table for keywords    
    mysql_query("CREATE TABLE search_keyword(
                       `id` INT NOT NULL auto increment,
                       `product_id` INT NOT NULL,
                       `keyword` VARCHAR(100) NOT NULL DEFAULT '',
                       PRIMARY KEY(`id`),
                       key `product_id` (`product_id`),
                      UNIQUE KEY `product_id_keyword`(`keyword`, `product_id`)
                )"
    );

    fgetcsv($handle1000",");

    //Insert Queries
    //Used ignore because there is a possibly the same product has the same keyword 
    //twice, and these will give a mysql warning because of the unique key on keyword
    //and product_id there for, the INSERT IGNORE will not do the insert and will not
    //prompt the warning
    $insert_keyword "INSERT IGNORE INTO `search_keyword` VALUES (NULL, %d, '%s')";
    $insert_product "INSERT INTO `product` VALUES (%d, '%s')";
    while ((
    $data fgetcsv($handle1000",")) !== FALSE) {
        
    $product_id array_shift($data);
        
    $product_name array_shift($data);
        
        
    //Insert product into product table
        
    mysql_query(sprintf($insert_product
                            
    mysql_real_escape_string(intval($product_id)), 
                            
    mysql_real_escape_string($product_name
                    ));
                    
        
    //Loop through the keywords and insert onto database
        
    foreach($data as $keyword){
            if( 
    strlen(trim($keyword)) ){
                
    mysql_query(sprintf($insert_keyword
                                    
    mysql_real_escape_string(intval($product_id)), 
                                    
    mysql_real_escape_string($keyword
                            ));
            }
        }
    }

    //Run Query to get all products, and their keywords
    $result mysql_query("SELECT p.id, p.name, group_concat(keyword) FROM product p 
                            JOIN search_keyword sk ON sk.product_id = p.id
                            GROUP BY p.id"
    );
    while (list(
    $id$name$keywords) = mysql_fetch_row($result)) {
        print 
    "Product_id: $id, Name: $name, Keywords: $keywords\n";
    }
    Okk, i used this as I already have a products table
    PHP Code:

    fgetcsv
    ($handle1000",");



    //Insert Queries

    //Used ignore because there is a possibly the same product has the same keyword 

    //twice, and these will give a mysql warning because of the unique key on keyword

    //and product_id there for, the INSERT IGNORE will not do the insert and will not

    //prompt the warning

    $insert_keyword "INSERT IGNORE INTO `Product_keywords` VALUES (NULL, %d, '%s')";


    while ((
    $data fgetcsv($handle1000",")) !== FALSE) {

        
    $product_id array_shift($data);

        
    $product_name array_shift($data);

        

                    

        
    //Loop through the keywords and insert onto database

        
    foreach($data as $keyword){

            if( 
    strlen(trim($keyword)) ){

                
    mysql_query(sprintf($insert_keyword

                                    
    mysql_real_escape_string(intval($product_id)), 

                                    
    mysql_real_escape_string($keyword

                            ));

            }

        }


    the result was..... no errors... no data inserted
    Any ideas?

    the csv looks like this:
    product_id - product_name - Keyword1 - Keyword2 - Keyword3 etc (upto keyword20)

  9. #9
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by adammc View Post
    Okk, i used this as I already have a products table
    the csv looks like this:
    product_id - product_name - Keyword1 - Keyword2 - Keyword3 etc (upto keyword20)
    CSV = Comma-Separated Values
    Where's the Commas?
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by adammc View Post
    Why not? What should the id be instead?
    the id shouldn't be there at all

    change this --
    Code:
    CREATE TABLE search_keyword
    ( id INTEGER NOT NULL AUTO_INCREMENT
    , product_id INTEGER NOT NULL
    , keyword VARCHAR(100) NOT NULL 
    , PRIMARY KEY( id )
    , KEY product_id_x ( product_id )
    , UNIQUE KEY product_id_keyword_x ( keyword , product_id )
    );
    to this --
    Code:
    CREATE TABLE search_keyword
    ( product_id INTEGER NOT NULL
    , keyword VARCHAR(100) NOT NULL 
    , PRIMARY KEY ( product_id , keyword )
    );
    please ask if you do not understand the reason for any of the suggested changes in this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mark Baker View Post
    CSV = Comma-Separated Values
    Where's the Commas?
    sorry Mark I meant that was an example of the excel columns b4 I export it as csv for use in the script

  12. #12
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the id shouldn't be there at all
    Thanks for the reply

    Ok, so using your db structure... Can you assist in getting the import script to work? I cant get it to insert a thing

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't do php, only SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •