insert into mysql table problems

Anon

Hello,

I'm trying to take the values from an array and put them into a mysql table. I keep gettng an error on the query no matter how I rewrite it. The script does parse without errors. I would appreciate anyone taking a look at the script and making suggestions on what could be the problem.

-----snip----

// Need to convert old date in unixtime to YYYY-MM-DD HH:MM

function convertdate($unixtime) {
return date("Y-m-d", $unixtime);
}

function converttime($unixtime) {
return date("h:m", $unixtime);
}

function convertDateTime($unixtime) {
return convertdate($unixtime)." ".converttime($unixtime);
}

// Open file and put in array
$data = file("babeltopics2.txt");

// Open connection
$newlink = mysql_pconnect("myserver", "myuser", "mypassword")

or die("Could not connect");
mysql_select_db("mydatabase", $newlink)
or die("Could not select database.");

// loop through array
// Elements in this form 952205057|952205057|952205057|7|435

while (list($key, $value) = each($data))

{
$newvalues = explode("|", $value)
or die("Explosion fizzled!");

for($i=0; $i < count($newvalues); $i++) {
$newvalues[2] = convertDateTime($newvalues[2]);

$newresult = mysql_query("INSERT INTO my_posts(post_id, topic_id, post_time, forum_id, poster_id) VALUES ('{$newvalues[0]}' , '{$newvalues[1]}' , '{$newvalues[2]}' , '{$newvalues[3]}' , '{$newvalues[4]}')", $newlink)
or die("Invalid query");

}

}
mysql_free_result($newresult);

mysql_close($newlink);

Thanks in advance for any help!

Susan

Anon

few things:
1. unless you are using them for other things elsewhere in your code, you don't need all those custom functions to create a formatted date from a timestamp. just do:
$newvalues[2] = date ('Y-m-d h:m', $newvalues[2]);

I don't think you want the for loop at all. It seems you are trying to insert element from each line of the txt file into records in the db so you don't need to iterate thru the individual elements of each line.
I don't think you need the {...} around your values in your SQL statement

try this:

<?
$data = file ('babeltopics2.txt');
// db connect code
foreach ($data as $value) {
$newvalues = explode('|', $value)
$newvalues[2] = date('Y-m-d h:m', $newvalues[2]);
$newresult = mysql_query ("INSERT INTO my_posts (post_id, topic_id, post_time, forum_id, poster_id) VALUES ('$newvalues[0]', '$newvalues[1]', '$newvalues[2]', '$newvalues[3]', '$newvalues[4]'");
}
?>

icaro

tell us what error you see.. i guess you are stoping at 'Invalid query', dont you?

first, try put a semicolon at end of query:
.... '{$newvalues[4]}');"
but it dont use to be a problem.

secont, try to (for test) remove that 'or die("Invalid query"); since we want to know exactly whats wrong with db, mysql will return a more especific error msg.

if dont, or it dont give you a clue, try this:

$newsquery = "INSERT INTO my_posts(post_id, topic_id, post_time, forum_id, poster_id) VALUES ('{$newvalues[0]}' , '{$newvalues[1]}' , '{$newvalues[2]}' , '{$newvalues[3]}' , '{$newvalues[4]}');";
echo $newsquery; // copy the result in your mysql server prompt (or phpmyadmin..) and execute it
$newresult = mysql_query($newsquery, $newlink);

hope it helps.

icaro

i agree about functions, but we dont know the rest of code... maybe it is usefull in other way.

but the {} around values are needed since it is an array called inside a string, the secure way to call it is with

{$arrayvar[index]}

Anon

Devin,

It was the for loop - since it was under the control of the while statement, it was attempting to insert the 1st row twice, causing a primary key violation in mysql. I finally figured out how to connect with debug on. :-)

The date time functions were recyled code and didn't cause any problems on their own.

Your code is much neater. I'm not that proficient of a coder. I am at the stage of looking up what functions do and clobbering them together. I didn't realize that PHP supported foreach. How does $data as $value translate into list($key, $value) = each($data)). There's no list in foreach $data as $value. I guess what I'm trying to ask is how foreach knows that $value is the value half of the key/value pair?

Thanks to all!

Susan Murray

Anon

the syntax to iterate thru values is:
foreach ($array as $value) {...do something...}

the syntax to iterate thru keys and values is:
foreach ($array as $key => $value) {...do something...}

In your example you don't care about the keys so there is no need to assign them to a variable in the foreach loop. Read about foreach:
http://www.php.net/manual/en/control-structures.foreach.php

I see many posts on this forum where people use while loops and for loops to traverse arrays which I never understand since PHP has a built-in construct specifically for this purpose.

yurko

as far as i remember
{} arround the array needed in PHP3
in PHP4 it is ok without them

yurko

I would like to help you, but I need to know exactly what error message you got.
Please add this "echo" after your insert:

########## this line:
echo mysql_errno().": ".mysql_error()."<BR>";
##########

Now execute your script and tell me (ctr+c ctr+v) what mysql sayz. Or mayb you already have it.
Then we will surelly make it work.
🙂

Anon

devin wrote:

I see many posts on this forum where people use while loops and for loops to traverse arrays which I never understand since PHP has a built-in construct specifically for this purpose.

I think it is because (as the url referenced in your post) states:

"PHP 4 (not PHP 3) includes a foreach construct."

Those of us who started in php3 and now are in php4 often don't know about the foreach construct. :-)

Cheers,

Susan