a tricky little mysql_query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

a tricky little mysql_query

Post by invisibled »

Hey Guys and Gals,

My Problem: I am building a system where users upload a csv file, on post that csv gets uploaded the server and then i need its contents to be put into a mysql_query function.

Currently i have everything working except for the mysql_query. I will post code and then explain it below.

Code: Select all

 
if(isset($_POST['csv'])):
    extract($_POST);
    
    $random = rand(100000,999999);
    $uploaddir = '../lib/uploads/file/products/';
    $uploadfile = $uploaddir . $random . '_' . basename($_FILES['userfile']['name']);
    if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile)) {
      
        if (($handle = fopen("$uploadfile", "r")) !== FALSE) {
            
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                $num = count($data);
 
                for ($c=15; $c < $num; $c++) {
                    $var .= "'$data[$c]', ";
                    
                }//for
        
            
                mysql_query("INSERT INTO $table VALUES (
                    '',
                    '$curUser',
                    $var
                    '$image'
                )") or die(mysql_error());
                
                
                
                
            }//while
            fclose($handle);
        }//if
    
    } else {
        print '<script type="text/javascript">alert("Error uploading file. Please try again.");</script>';
    }
endif;
 
so if you look in the for loop, i have that $var variable storing each loop of data and then i put var into the query. if i print var normally, it prints out all the fields properly. But when i put it in the query it gives me a syntax error or column count error. This leads me to belive that i'm almost there, i just need to format it differently. Any Suggestions?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: a tricky little mysql_query

Post by Eran »

column count error
This means the number of columns in the table and in the insert statement mismatch. You need to either provide a value for each column in the table, or specify the columns you are inserting for before the VALUES statement (as it's the common way to do it).
[sql]INSERT INTO $table (col1,col2,col3,col4) VALUES ('','$curUser',$var,'$image')[/sql]
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: a tricky little mysql_query

Post by invisibled »

hmm, so i did that

Code: Select all

 
mysql_query("INSERT INTO $table ('id', 'user_id', 'item_name', 'menu', 'category', 'components', 'selling_tips', 'service_ware', 'pairing_suggestions', 'price', 'link', 'dairy', 'pork', 'nuts', 'shellfish', 'wheat', 'vegetarian', 'image') VALUES ('', '$curUser', $var, '$image')") or die(mysql_error());
 
and it gives me this error:

Code: Select all

 
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 ''id', 'user_id', 'item_name', 'menu', 'category', 'components', 'selling_tips', ' at line 1
 
and var i now have formatted like so, to reflect the comma being in the query.

Code: Select all

 
 $var .= "'$data[$c]'";
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: a tricky little mysql_query

Post by Eran »

The variable $table is probably empty or isn't set. Check what it contains
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: a tricky little mysql_query

Post by invisibled »

it contains the table name, its a cms system i've built. But i checked it anyways and it contains the right table name. Is their any way i could print out the output of the query, like what phpmyadmin does?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: a tricky little mysql_query

Post by Eran »

put the query inside a variable and var_dump() it. put the results here.
Last edited by Eran on Fri Jan 08, 2010 8:52 pm, edited 1 time in total.
invisibled
Forum Contributor
Posts: 112
Joined: Sun Apr 29, 2007 3:35 pm
Location: New Westminster

Re: a tricky little mysql_query

Post by invisibled »

var_dump of $var

Code: Select all

 
"'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', " 
 
var dump of the query just says bool(true) . This is how i did it (replaced the $var with the empty fields otherwise it just errors).

Code: Select all

 
$query = mysql_query("INSERT INTO $table VALUES ('', '$curUser', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '$image')") or die(mysql_error());
                print var_dump($query);
 
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: a tricky little mysql_query

Post by Eran »

No mate, put the contents of the query itself (the string you put in the function mysql_query) inside a variable and dump its contents.
Post Reply