Inspiring

Question

OT: A PHP Punt! Inserting arrays into data tables....

Forum|Forum|19 years ago
February 28, 2007
17 replies
925 views

OK - I've beat my head to a pulp.

I have an array of paired strings. I want to add the whole array to a data
table, in alternating fields.

I have this -

mysql_select_db($database_cmaConn, $cmaConn);

$fields = "'keyJargon','keyDef'";
$values = "'".implode(array_values($toAdd), "','")."'";

function mysql_insert($table,$ToAdd) {
$q = 'INSERT INTO `'.$table.'` ('.$fields.') VALUES ('.$values.')';
$res = mysql_query($q)OR die(mysql_error());

return true;

} //end function

if (isset($_POST['Submit'])) {

$jargonArray=explode("\n",$_POST['upload']);
// this splits the input data into consecutive elements of the array, one
for each new line.

$i=0;
foreach($jargonArray as $key => $value) {
if (strlen($value) > 0) {
$jargon[] = explode("\x09",$value);
//this splits the tab-delimited input data into adjacent array elements.

mysql_insert('cma_keywords',$jargon[$i][0][1]);
$i++;
} // endif

}

But it's broke. What am I doing wrong?

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

Server side applications

This topic is closed to new replies. Start a new post to keep the conversation going.

N

Newsgroup_UserAuthor

Inspiring

On Thu, 01 Mar 2007 22:25:29 +0100, Michael Fesser <netizen@gmx.de>
wrote:

>Using a single INSERT may cause other problems, depdending on how many
>records you want to insert at once. It also requires some CPU power and
>probably more memory, because all the data has to be transfered to the
>DB server in a large single block. Additionally you might reach some
>internal length or buffer limit, causing the query to fail. I did that a
>while ago - using separate INSERTs fixed the problem.

I've used the technique to insert a few hundred records at once without
issue. I agree that it is not without limits, but it does save making a
few hundred separate requests to the db server.

Gary

N

Newsgroup_UserAuthor

Inspiring

.oO(Gary White)

>On Wed, 28 Feb 2007 18:29:53 -0500, "Murray *ACE*"
><forums@HAHAgreat-web-sights.com> wrote:
>
>>But it's broke. What am I doing wrong?
>
>Why are you doing the insert inside a loop? That makes it very processor
>intensive and probably slow.

I don't think so (of course it could be improved a bit by using a
prepared statement, which can be executed multiple times, just with
different values).

>Just build your SQL statement and insert
>them all at once. Again, (I think) we're just seeing a snippet of the
>code, but consider this SQL statement:
>
>INSERT INTO yourtable (keyJargon,keyDef)
>VALUES ('something','blah'),('something else','more blah')
>
>That would insert two records in a single database action. If you have
>trouble with it, send me the file off list and I'll take a look.

Using a single INSERT may cause other problems, depdending on how many
records you want to insert at once. It also requires some CPU power and
probably more memory, because all the data has to be transfered to the
DB server in a large single block. Additionally you might reach some
internal length or buffer limit, causing the query to fail. I did that a
while ago - using separate INSERTs fixed the problem.

Micha

N

Newsgroup_UserAuthor

Inspiring

.oO(Murray *ACE*)

>Yes - that works with slight modification -
>
>$q = 'INSERT INTO cma_keywords (keyJargon,keyDef) VALUES ('."'";
>$q .= trim($jargon[0]);
>$q .= "','";
>$q .= trim($jargon[1]);
>$q .= "');";
>
>Encaps problems....

I would do it slightly different:

$queryStr = "INSERT INTO cma_keywords (keyJargon, keyDef) VALUES ('%s', '%s')";
$query = sprintf($queryStr,
mysql_real_escape_string(trim($jargon[0])),
mysql_real_escape_string(trim($jargon[1]))
);

For at least two reasons:

* IMHO better readable code, which is easier to maintain and extend
* never forget to escape the data before inserting it into the DB

Micha

N

Newsgroup_UserAuthor

Inspiring

On Thu, 1 Mar 2007 12:06:59 -0500, "Murray *ACE*"
<forums@HAHAgreat-web-sights.com> wrote:

>I don't know how many records my client will have to upload at any given
>time.

It doesn't matter. See my reply to Joe.

Gary

N

Newsgroup_UserAuthor

Inspiring

On Thu, 1 Mar 2007 17:10:31 +0000 (UTC), Joe Makowiec
<makowiec@invalid.invalid> wrote:

>Gary's got a point here. Build the string inside the loop, then run the query.

I'd probably approach it more like this:

if (isset($_POST['Submit'])) {

mysql_select_db($database_cmaConn, $cmaConn);

$jargonArray=explode("\n",$_POST['upload']);
// this splits the input data into consecutive elements
// of the array, one for each new line.

$q = 'INSERT INTO cma_keywords (keyJargon,keyDef) VALUES ';
$jargon=array();
foreach($jargonArray as $value) {
if (strlen($value) > 0) {
$jargon[] = "('".preg_replace("/\t/","','",$value)."')";
} // endif strlen
} // end foreach
$q.=join(",",$jargon);

// just to show you what the query looks like
print "<pre>$q</pre>\n";

// Do the query
$res = mysql_query($q) OR die(mysql_error());
// Do something with $res here - check if insert went OK
} // end isset

Gary

N

Newsgroup_UserAuthor

Inspiring

On 01 Mar 2007 in macromedia.dreamweaver.appdev, Gary White wrote:

> Why are you doing the insert inside a loop? That makes it very
> processor intensive and probably slow. Just build your SQL statement
> and insert them all at once. Again, (I think) we're just seeing a
> snippet of the code, but consider this SQL statement:
>
> INSERT INTO yourtable (keyJargon,keyDef)
> VALUES ('something','blah'),('something else','more blah')
>
> That would insert two records in a single database action. If you
> have trouble with it, send me the file off list and I'll take a
> look.

Gary's got a point here. Build the string inside the loop, then run the query.

mysql_select_db($database_cmaConn, $cmaConn);

if (isset($_POST['Submit'])) {
$jargonArray=explode("\n",$_POST['upload']);
// this splits the input data into consecutive elements of the array, one for each new line.

$q = 'INSERT INTO cma_keywords (keyJargon,keyDef) VALUES ';
foreach($jargonArray as $key => $value) {
if (strlen($value) > 0) {
$jargon = explode("\x09",$value);
//this splits the tab-delimited input data into adjacent array elements.
$q .= ('."'";
$q .= trim($jargon[0]);
$q .= "','";
$q .= trim($jargon[1]);
$q .= "'), ";
} // endif strlen
} // end foreach
} // endif isset
// String now ends with ", " (comma space); we need to get rid of that and add a semicolon
$q = substr($q, 0, strlen($q) - 2) . ';';
// Do the query
$res = mysql_query($q) OR die(mysql_error());
// Do something with $res here - check if insert went OK

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

N

Newsgroup_UserAuthor

Inspiring

I don't know how many records my client will have to upload at any given
time.

I suppose I could build up the string with the records explicitly called
out, though, and then execute a single insert statement. Would there be a
significant difference in behavior?

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"Gary White" <reply@newsgroup.please> wrote in message
news:patdu2d5dnnoglkhcc66tstakd0bbggljn@4ax.com...
> On Wed, 28 Feb 2007 18:29:53 -0500, "Murray *ACE*"
> <forums@HAHAgreat-web-sights.com> wrote:
>
>>But it's broke. What am I doing wrong?
>
> Why are you doing the insert inside a loop? That makes it very processor
> intensive and probably slow. Just build your SQL statement and insert
> them all at once. Again, (I think) we're just seeing a snippet of the
> code, but consider this SQL statement:
>
> INSERT INTO yourtable (keyJargon,keyDef)
> VALUES ('something','blah'),('something else','more blah')
>
> That would insert two records in a single database action. If you have
> trouble with it, send me the file off list and I'll take a look.
>
> Gary

N

Newsgroup_UserAuthor

Inspiring

On Wed, 28 Feb 2007 18:29:53 -0500, "Murray *ACE*"
<forums@HAHAgreat-web-sights.com> wrote:

>But it's broke. What am I doing wrong?

Why are you doing the insert inside a loop? That makes it very processor
intensive and probably slow. Just build your SQL statement and insert
them all at once. Again, (I think) we're just seeing a snippet of the
code, but consider this SQL statement:

INSERT INTO yourtable (keyJargon,keyDef)
VALUES ('something','blah'),('something else','more blah')

That would insert two records in a single database action. If you have
trouble with it, send me the file off list and I'll take a look.

Gary

N

Newsgroup_UserAuthor

Inspiring

Yessir.

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.dreamweavermx-templates.com - Template Triage!
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
http://www.macromedia.com/support/search/ - Macromedia (MM) Technotes
==================

"Joe Makowiec" <makowiec@invalid.invalid> wrote in message
news:Xns98E65806BF1B0makowiecatnycapdotrE@216.104.212.96...
> On 01 Mar 2007 in macromedia.dreamweaver.appdev, Murray *ACE* wrote:
>
>> Maybe I should have said "Gelcaps problems". 8)
>>
>> There were quote problems on the field values, which I fixed in the
>> code I posted.
>
> Gotcha. Trimmed the values and enclosed them in quotes.
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/email.php

N

Newsgroup_UserAuthor

Inspiring

On 01 Mar 2007 in macromedia.dreamweaver.appdev, Murray *ACE* wrote:

> Maybe I should have said "Gelcaps problems". 8)
>
> There were quote problems on the field values, which I fixed in the
> code I posted.

Gotcha. Trimmed the values and enclosed them in quotes.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

Show more replies

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.