SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using mysql instead of php

    I have a small PHP script that I think would be more efficiently processed directly in MySQL:

    Code PHP:
    $sql = "SELECT DISTINCT created, updated, idcode, office, model, size, material, conn, pressure, ex3, ex4, ex5, ex6 FROM `".IDCODE_TBL."` ORDER BY office DESC, model, size, material, conn, pressure, ex3, ex4, ex5, ex6";
    $result = mysql_query($sql);
    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    	if((strlen(trim($row['size'])) >0) || (strlen(trim($row['material'])) >0) || (strlen(trim($row['conn'])) >0) || (strlen(trim($row['pressure'])) >0) || (strlen(trim($row['ex3'])) >0) || (strlen(trim($row['ex4'])) >0) || (strlen(trim($row['ex5'])) >0) || (strlen(trim($row['ex6'])) >0)){
    		$key = $row['office']." ".$row['model'];
    		$val =  $key." ".trim($row['size'])." ".trim($row['material'])." ".trim($row['conn'])." ".trim($row['pressure'])." ".trim($row['ex3'])." ".trim($row['ex4'])." ".trim($row['ex5'])." ".trim($row['ex6']);	
    		$ins_sql = "INSERT INTO `".MODEL_TBL."` VALUES ('".$row['idcode']."', '".$key."', '".$val."')";	
    		$ins_result = mysql_query($ins_sql);	
    	} 
    }

    My SQL skills are a bit weak, does anyone know how to do the above directly in SQL?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    for those of us who don't do php, what does that piece o' code actually do?

    this being the mysql forum and all...

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

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    basically it does this:

    a. Select distrinct items from IDCODE_TBL table.
    b. For each row, if any of 'size', 'material' etc is not null, then do this:
    b1. set $key as office.model
    b2. set $val as $key.'size'.'material' etc (trimming any whitespace at the same time)
    b3. insert $key and $val to the MODEL_TBL table.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, thanks, that helped

    yes, you can do this all inside mysql --
    Code:
    INSERT 
      INTO `".MODEL_TBL."` 
    SELECT idcode
         , key
         , val
      FROM ( SELECT idcode
                  , CONCAT(office,' ',model) AS key
                  , CONCAT(office,' ',model,' ',TRIM(size)) AS val
               FROM `".IDCODE_TBL."` 
              WHERE TRIM(size) > 0
             UNION ALL
             SELECT idcode
                  , CONCAT(office,' ',model) AS key
                  , CONCAT(office,' ',model,' ',TRIM(material)) AS val
               FROM `".IDCODE_TBL."` 
              WHERE TRIM(material) > 0
             UNION ALL
             ...
             SELECT idcode
                  , CONCAT(office,' ',model) AS key
                  , CONCAT(office,' ',model,' ',TRIM(ex6)) AS val
               FROM `".IDCODE_TBL."` 
              WHERE TRIM(ex6) > 0
             ) AS d
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ooo

    Thanks, I will give it a go!

    My script was taking 40 mins to run so hopefully this will speed it up a lot.


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
  •