Thread: PHP Code Help

Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    83
    Rep Power
    11

    PHP Code Help


    Hello,

    I posted a question in the MySQL thread hxxp://forums.devshed.com/mysql-help-4/newbie-help-610863.html (cant link to the original replace the xx's)

    in that thread I was told that my question is a PHP Question, If someone could help me I would greatly appreciate it, When helping please understand that I am just learning and if you could explain how to fix the problem and not necessarily tell me the fix so I can understand what is wrong and how exactly I go about fixing it.

    Thank you so much for your help.

    Derrick
  2. #2
  3. Headless Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,937
    Rep Power
    9647
    two invisible characters
    Originally Posted by technix1
    I have the following code:
    PHP Code:
    $target_path "uploads/";

    $target_path $target_path basename$_FILES['uploadedfile']['name']); 


    if(
    move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
        
        require_once 
    '../Excel/reader.php';
        
        
    $data = new Spreadsheet_Excel_Reader();
        
        
    $data->setOutputEncoding('CP1251');
        
        
    $data->read($target_path);

    for (
    $i 2$i <= $data->sheets[0]['numRows']; $i++) {
    $sql =  "insert into coupons values ('',";
    for (
    $j 1$j <= $data->sheets[0]['numCols']; $j++) {              

    if (
    $j == $data->sheets[0]['numCols'])

    $sql .= "'".$data->sheets[0]['cells'][$i][$j]."'";
    }
    else if (
    $j == || $j == 4){

    $parts explode("/"$data->sheets[0]['cells'][$i][$j]);

    $sql .= "'".$parts[2]."-".$parts[1]."-".$parts[0]."',";                  

    }

    else {
    $sql .= "'".$data->sheets[0]['cells'][$i][$j]."',";

    }
    $sql .=")"
    mysql_query($sql);
    echo 
    $sql."<br>"
    which will echo the following

    insert into coupons values ('','Detroit, MI','Kmart','2009-03-14','2009-04-15','00089887','Pepsi','1 - 2 Liter','0.67','1.49','Double','1','Y') insert into coupons values ('','Detroit, MI','Kroger','2009-03-14','2009-04-15','00089887','Pepsi','1 - 2 Liter','0.99','1.49','Double','0.5','N') insert into coupons values ('','Detroit, MI','Meijer','2009-03-14','2009-04-15','00089887','Pepsi','1 - 2 Liter','0.47','1.49','None','0.5','N')
    This is uploaded via an xls spreadsheet where the first row is header data,

    My problem is even though it will echo it, its not inserting it into the db.

    any help is greatly appreciated.

    Derrick
  4. #3
  5. Headless Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,937
    Rep Power
    9647
    insert into coupons values ('','Detroit, MI','Kmart','2009-03-14','2009-04-15','00089887','Pepsi','1 - 2 Liter','0.67','1.49','Double','1','Y') insert into coupons values ('','Detroit, MI','Kroger','2009-03-14','2009-04-15','00089887','Pepsi','1 - 2 Liter','0.99','1.49','Double','0.5','N') insert into coupons values ('','Detroit, MI','Meijer','2009-03-14','2009-04-15','00089887','Pepsi','1 - 2 Liter','0.47','1.49','None','0.5','N')
    I see three queries in there.

    mysql_query cannot run three queries at once - you need three separate calls.
    In your case these INSERTs are inside a loop. This means the mysql_query($sql) needs to happen inside that very same loop; then it will run one query at a time.

    Comments on this post

    • lnxgeek agrees : Hmm, why does this look so familiar? :p
  6. #4
  7. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    797
    I adjusted your statement structure just a little and added in some comments to explain what is going on. Let me know if you understand what I outlined here.
    PHP Code:
    //start the initial statement here
    $sql 'insert into coupons values ';

    //loop through each row on this specific sheet
    for ($row 2$row <= $data->sheets[0]['numRows']; $row++) {
        
    //start a new "values" section
        
    $sql .= "('',";

        
    //loop through the column on this row
        
    for ($column 1$column <= $data->sheets[0]['numCols']; $column++) {
            if (
    $column $data->sheets[0]['numCols']) {
                
    $sql .= "'" $data->sheets[0]['cells'][$row][$column]."'";
            } else if (
    $column == || $column == 4) {
                
    $parts explode('/'$data->sheets[0]['cells'][$row][$column]);
                
    $sql .= "'".$parts[2]."-".$parts[1]."-".$parts[0]."',";
            } else {
                
    $sql .= "'".$data->sheets[0]['cells'][$row][$column]."',";
            }
        }

        
    //close the existing "values" section
        
    $sql .= ')';

        
    //determine if we need to add another "values" section to the statement
        
    if ($row != $data->sheets[0]['numRows']) {
            
    $sql .= ',';
        }

  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    83
    Rep Power
    11
    Originally Posted by Onslaught
    I adjusted your statement structure just a little and added in some comments to explain what is going on. Let me know if you understand what I outlined here.
    PHP Code:
    //start the initial statement here
    $sql 'insert into coupons values ';

    //loop through each row on this specific sheet
    for ($row 2$row <= $data->sheets[0]['numRows']; $row++) {
        
    //start a new "values" section
        
    $sql .= "('',";

        
    //loop through the column on this row
        
    for ($column 1$column <= $data->sheets[0]['numCols']; $column++) {
            if (
    $column $data->sheets[0]['numCols']) {
                
    $sql .= "'" $data->sheets[0]['cells'][$row][$column]."'";
            } else if (
    $column == || $column == 4) {
                
    $parts explode('/'$data->sheets[0]['cells'][$row][$column]);
                
    $sql .= "'".$parts[2]."-".$parts[1]."-".$parts[0]."',";
            } else {
                
    $sql .= "'".$data->sheets[0]['cells'][$row][$column]."',";
            }
        }

        
    //close the existing "values" section
        
    $sql .= ')';

        
    //determine if we need to add another "values" section to the statement
        
    if ($row != $data->sheets[0]['numRows']) {
            
    $sql .= ',';
        }

    Thank you for the help, I see the code, and I see how it is commented

    I took the code and this is what my new code looks like

    PHP Code:
    <?php

    include_once("../connection_db.php");


    $target_path "uploads/";

    $target_path $target_path basename$_FILES['uploadedfile']['name']); 


    if(
    move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
        
        require_once 
    '../Excel/reader.php';
        
        
    $data = new Spreadsheet_Excel_Reader();
        
        
    $data->setOutputEncoding('CP1251');
        
        
    $data->read($target_path);
        
        
        
        
    //start the initial statement here
    $sql 'insert into coupons values ';

    //loop through each row on this specific sheet
    for ($row 2$row <= $data->sheets[0]['numRows']; $row++) {
        
    //start a new "values" section
        
    $sql .= "('',";

        
    //loop through the column on this row
        
    for ($column 1$column <= $data->sheets[0]['numCols']; $column++) {
            if (
    $column $data->sheets[0]['numCols']) {
                
    $sql .= "'" $data->sheets[0]['cells'][$row][$column]."'";
            } else if (
    $column == || $column == 4) {
                
    $parts explode('/'$data->sheets[0]['cells'][$row][$column]);
                
    $sql .= "'".$parts[2]."-".$parts[1]."-".$parts[0]."',";
            } else {
                
    $sql .= "'".$data->sheets[0]['cells'][$row][$column]."',";
            }
        }

        
    //close the existing "values" section
        
    $sql .= ')';

        
    //determine if we need to add another "values" section to the statement
        
    if ($row != $data->sheets[0]['numRows']) {
            
    $sql .= ',';
       
       }}else{
        echo 
    "There was an error uploading the file, please try again!";
    }





    ?>
    and I get this error

    Parse error: syntax error, unexpected T_ELSE in /home/rosebud1/public_html/wizard/admin/scripts/addFile.php on line 51
    So I assume I have either an extra curly bracket or not enough.

    Sorry if the post doesnt process the code in proper spacing.

    Thank you again for all the help,
  10. #6
  11. Headless Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,937
    Rep Power
    9647
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    83
    Rep Power
    11
    Originally Posted by requinix
    PHP Code:
    }}else{ 
    Thank you I thought that there was ony spose to be }else{ and tried that which gave me

    here is the new code

    PHP Code:
    <?php

    include_once("../connection_db.php");


    $target_path "uploads/";

    $target_path $target_path basename$_FILES['uploadedfile']['name']); 


    if(
    move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
        
        require_once 
    '../Excel/reader.php';
        
        
    $data = new Spreadsheet_Excel_Reader();
        
        
    $data->setOutputEncoding('CP1251');
        
        
    $data->read($target_path);
        
        
        
        
    //start the initial statement here
    $sql 'insert into coupons values ';

    //loop through each row on this specific sheet
    for ($row 2$row <= $data->sheets[0]['numRows']; $row++) {
        
    //start a new "values" section
        
    $sql .= "('',";

        
    //loop through the column on this row
        
    for ($column 1$column <= $data->sheets[0]['numCols']; $column++) {
            if (
    $column $data->sheets[0]['numCols']) {
                
    $sql .= "'" $data->sheets[0]['cells'][$row][$column]."'";
            } else if (
    $column == || $column == 4) {
                
    $parts explode('/'$data->sheets[0]['cells'][$row][$column]);
                
    $sql .= "'".$parts[2]."-".$parts[1]."-".$parts[0]."',";
            } else {
                
    $sql .= "'".$data->sheets[0]['cells'][$row][$column]."',";
            }
        }

        
    //close the existing "values" section
        
    $sql .= ')';
        
    //determine if we need to add another "values" section to the statement
        
    if ($row != $data->sheets[0]['numRows']) {
           
    $sql .= ',';
       
    mysql_query($sql);
            }else{
        echo 
    "There was an error uploading the file, please try again!";
    }

    }
    }


    ?>
    I added the mysql_query as I forgot that previously so even if there were no other errors it wouldnt actually post.

    I now get there error "there was an error uploading the file, please try again! which is the Echo from the Else.

    Here is the test.xls file I am using, I think that the code above is not getting all the columns,

    Market Store C_Start C_End UPC Description Size Sale Price Retail Price Store Coupons Store Max Card Required
    Detroit, MI Kmart 13/03/2009 14/04/2009 00089887 Pepsi 1 - 2 Liter 0.67 1.49 Double 1 Y
    Detroit, MI Kroger 13/03/2009 14/04/2009 00089887 Pepsi 1 - 2 Liter 0.99 1.49 Double 0.5 N
    Detroit, MI Meijer 13/03/2009 14/04/2009 00089887 Pepsi 1 - 2 Liter 0.47 1.49 None 0.5 N
  14. #8
  15. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    797
    no, there is no else segment to that if statement. It is meant only to add a comma after each values section. Execute the $sql statement outside of the for-loops.
    e.g.
    PHP Code:
        //determine if we need to add another "values" section to the statement
        
    if ($row != $data->sheets[0]['numRows']) {
            
    $sql .= ',';
        }
    }  
    if (isset(
    $sql)) {
        
    mysql_query($sql) or die(mysql_error());

  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    83
    Rep Power
    11
    Originally Posted by Onslaught
    no, there is no else segment to that if statement. It is meant only to add a comma after each values section. Execute the $sql statement outside of the for-loops.
    e.g.
    PHP Code:
        //determine if we need to add another "values" section to the statement
        
    if ($row != $data->sheets[0]['numRows']) {
            
    $sql .= ',';
        }
    }  
    if (isset(
    $sql)) {
        
    mysql_query($sql) or die(mysql_error());

    I greatly appreciate you helping me, I have this code as follows

    PHP Code:
    <?php

    include_once("../connection_db.php");


    $target_path "uploads/";

    $target_path $target_path basename$_FILES['uploadedfile']['name']); 


    if(
    move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
        
        require_once 
    '../Excel/reader.php';
        
        
    $data = new Spreadsheet_Excel_Reader();
        
        
    $data->setOutputEncoding('CP1251');
        
        
    $data->read($target_path);
        
        
        
        
    //start the initial statement here
    $sql 'insert into coupons values ';

    //loop through each row on this specific sheet
    for ($row 2$row <= $data->sheets[0]['numRows']; $row++) {
        
    //start a new "values" section
        
    $sql .= "('',";

        
    //loop through the column on this row
        
    for ($column 1$column <= $data->sheets[0]['numCols']; $column++) {
            if (
    $column $data->sheets[0]['numCols']) {
                
    $sql .= "'" $data->sheets[0]['cells'][$row][$column]."'";
            } else if (
    $column == || $column == 4) {
                
    $parts explode('/'$data->sheets[0]['cells'][$row][$column]);
                
    $sql .= "'".$parts[2]."-".$parts[1]."-".$parts[0]."',";
            } else {
                
    $sql .= "'".$data->sheets[0]['cells'][$row][$column]."',";
            }
        }

        
    //close the existing "values" section
        
    $sql .= ')';
        
    //determine if we need to add another "values" section to the statement
        
    if ($row != $data->sheets[0]['numRows']) {
            
    $sql .= ',';
        }
    }  
    if (isset(
    $sql)) {
        
    mysql_query($sql) or die(mysql_error());
    }  

    ?>
    and yet this is the same error Im getting, I hope as I read more, this stuff gets easier, that or im really ignorant.

    Parse error: syntax error, unexpected $end in /home/rosebud1/public_html/wizard/admin/scripts/addFile.php on line 55
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    83
    Rep Power
    11
    ok I fixed that error as I missed one of the } at the bottom,

    now i am presented with this error.

    Column count doesn't match value count at row 1


    does the comma, in my xls cell for "Detroit,MI" throw it off somehow?
  20. #11
  21. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    797
    Your initial if statement has been left open.
    PHP Code:
    if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) { 
    Any time you have an open brace { you have to have a closing one }
  22. #12
  23. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    797
    Echo out the query so we can see, then compare the number of columns inserted against the number of columns in your table.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    83
    Rep Power
    11
    Originally Posted by Onslaught
    Echo out the query so we can see, then compare the number of columns inserted against the number of columns in your table.
    ok I did the echo like such,

    PHP Code:
    if (isset($sql)) {
        echo 
    $sql."<br>";
        
    mysql_query($sql) or die(mysql_error());
    }  
    }
    ?> 
    which displayed this,

    Code:
    insert into coupons values ('','Y'),('','N'),('','N')
    Column count doesn't match value count at row 1

    this is my file data again,
    Code:
    Market Store C_Start C_End UPC Description Size Sale Price Retail Price Store Coupons Store Max Card Required
    Detroit, MI Kmart 13/03/2009 14/04/2009 00089887 Pepsi 1 - 2 Liter 0.67 1.49 Double 1 Y
    Detroit, MI Kroger 13/03/2009 14/04/2009 00089887 Pepsi 1 - 2 Liter 0.99 1.49 Double 0.5 N
    Detroit, MI Meijer 13/03/2009 14/04/2009 00089887 Pepsi 1 - 2 Liter 0.47 1.49 None 0.5 N
    Detroit, MI is in one cell for Market.
  26. #14
  27. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    797
    I see a typo
    change
    PHP Code:
    if ($column $data->sheets[0]['numCols']) { 
    to
    PHP Code:
    if ($column == $data->sheets[0]['numCols']) { 
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    83
    Rep Power
    11
    Originally Posted by Onslaught
    I see a typo
    change
    PHP Code:
    if ($column $data->sheets[0]['numCols']) { 
    to
    PHP Code:
    if ($column == $data->sheets[0]['numCols']) { 
    With your genius we are so close to being where it should be, thank you so much,

    this is the new error from echo.

    Code:
    insert into coupons values ('','Detroit, MI','Kmart','2009-03-14','2009-04-15','00089887','Pepsi','1 - 2 Liter','0.67','1.49','Double','1','Y'),('','Detroit, MI','Kroger','2009-03-14','2009-04-15','00089887','Pepsi','1 - 2 Liter','0.99','1.49','Double','0.5','N'),('','Detroit, MI','Meijer','2009-03-14','2009-04-15','00089887','Pepsi','1 - 2 Liter','0.47','1.49','None','0.5','N')
    Column count doesn't match value count at row 1
    so it looks like it has the cell data now but still saing count doesnt match
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo