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

Report · Feb 28, 2007

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
==================

Report · Feb 28, 2007

On 28 Feb 2007 in macromedia.dreamweaver.appdev, Murray *ACE* wrote:

> 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?

Don't have a boatload of time at the moment, and I know what grief we
went through yesterday getting stuff /into/ that jargon array, but...
You don't need it.

Try this:

mysql_select_db($database_cmaConn, $cmaConn);

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 = 'INSERT INTO `cma_keywords` (keyJargon,keyDef) VALUES (';
$q .= $jargon[0];
$q .= ',';
$q .= $jargon[1];
$q .= ');';
$res = mysql_query($q) OR die(mysql_error());
// Do something with $res here - check if insert went OK
} // endif
}// end foreach

Untested. Yeah, I know - it's case specific. I'm tired.

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

Report · Mar 01, 2007

So, if there were 25 pairs of word/definitions, I would just create a $q
that listed each explicitly?

Wouldn't it make more sense to have the insert in the loop?

--
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:Xns98E5D9D4A93E3makowiecatnycapdotrE@216.104.212.96...
> On 28 Feb 2007 in macromedia.dreamweaver.appdev, Murray *ACE* wrote:
>
>> 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?
>
> Don't have a boatload of time at the moment, and I know what grief we
> went through yesterday getting stuff /into/ that jargon array, but...
> You don't need it.
>
> Try this:
>
> mysql_select_db($database_cmaConn, $cmaConn);
>
> 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 = 'INSERT INTO `cma_keywords` (keyJargon,keyDef) VALUES (';
> $q .= $jargon[0];
> $q .= ',';
> $q .= $jargon[1];
> $q .= ');';
> $res = mysql_query($q) OR die(mysql_error());
> // Do something with $res here - check if insert went OK
> } // endif
> }// end foreach
>
> Untested. Yeah, I know - it's case specific. I'm tired.
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/email.php

Report · Mar 01, 2007

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

> So, if there were 25 pairs of word/definitions, I would just create
> a $q that listed each explicitly?
>
> Wouldn't it make more sense to have the insert in the loop?

It is inside the loop. See if this is clearer:

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.

foreach($jargonArray as $key => $value) {
if (strlen($value) > 0) {
$jargon = explode("\x09",$value);
//this splits the tab-delimited input data into adjacent array elements.
// Create the query string
// Should wind up looking like
// INSERT INTO `cma_keywords` (keyJargon,keyDef) VALUES (val1,val2);
$q = 'INSERT INTO `cma_keywords` (keyJargon,keyDef) VALUES (';
$q .= $jargon[0];
$q .= ',';
$q .= $jargon[1];
$q .= ');';
// Do the query
$res = mysql_query($q) OR die(mysql_error());
// Do something with $res here - check if insert went OK
} // endif strlen
} // end foreach
} // endif isset

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

Report · Mar 01, 2007

Ahh - I see. Lemme try it!

--
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:Xns98E65158497AEmakowiecatnycapdotrE@216.104.212.96...
> On 01 Mar 2007 in macromedia.dreamweaver.appdev, Murray *ACE* wrote:
>
>> So, if there were 25 pairs of word/definitions, I would just create
>> a $q that listed each explicitly?
>>
>> Wouldn't it make more sense to have the insert in the loop?
>
> It is inside the loop. See if this is clearer:
>
> 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.
>
> foreach($jargonArray as $key => $value) {
> if (strlen($value) > 0) {
> $jargon = explode("\x09",$value);
> //this splits the tab-delimited input data into adjacent array elements.
> // Create the query string
> // Should wind up looking like
> // INSERT INTO `cma_keywords` (keyJargon,keyDef) VALUES (val1,val2);
> $q = 'INSERT INTO `cma_keywords` (keyJargon,keyDef) VALUES (';
> $q .= $jargon[0];
> $q .= ',';
> $q .= $jargon[1];
> $q .= ');';
> // Do the query
> $res = mysql_query($q) OR die(mysql_error());
> // Do something with $res here - check if insert went OK
> } // endif strlen
> } // end foreach
> } // endif isset
>
>
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/email.php

Report · Mar 01, 2007

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....

--
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:Xns98E65158497AEmakowiecatnycapdotrE@216.104.212.96...
> On 01 Mar 2007 in macromedia.dreamweaver.appdev, Murray *ACE* wrote:
>
>> So, if there were 25 pairs of word/definitions, I would just create
>> a $q that listed each explicitly?
>>
>> Wouldn't it make more sense to have the insert in the loop?
>
> It is inside the loop. See if this is clearer:
>
> 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.
>
> foreach($jargonArray as $key => $value) {
> if (strlen($value) > 0) {
> $jargon = explode("\x09",$value);
> //this splits the tab-delimited input data into adjacent array elements.
> // Create the query string
> // Should wind up looking like
> // INSERT INTO `cma_keywords` (keyJargon,keyDef) VALUES (val1,val2);
> $q = 'INSERT INTO `cma_keywords` (keyJargon,keyDef) VALUES (';
> $q .= $jargon[0];
> $q .= ',';
> $q .= $jargon[1];
> $q .= ');';
> // Do the query
> $res = mysql_query($q) OR die(mysql_error());
> // Do something with $res here - check if insert went OK
> } // endif strlen
> } // end foreach
> } // endif isset
>
>
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/email.php

Report · Mar 01, 2007

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

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

Glad it worked. I knew I was tired when I wrote the post last night, and
got sloppy.

> Encaps problems....

Huh?

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

Report · Mar 01, 2007

Maybe I should have said "Gelcaps problems". 8)

There were quote problems on the field values, which I fixed in the code I
posted.

--
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:Xns98E656BB584C6makowiecatnycapdotrE@216.104.212.96...
> On 01 Mar 2007 in macromedia.dreamweaver.appdev, Murray *ACE* wrote:
>
>> Yes - that works with slight modification -
>>
>> $q = 'INSERT INTO cma_keywords (keyJargon,keyDef) VALUES ('."'";
>> $q .= trim($jargon[0]);
>> $q .= "','";
>> $q .= trim($jargon[1]);
>> $q .= "');";
>
> Glad it worked. I knew I was tired when I wrote the post last night, and
> got sloppy.
>
>> Encaps problems....
>
> Huh?
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/email.php

Report · Mar 01, 2007

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

Report · Mar 01, 2007

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

Report · Mar 01, 2007

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

Report · Mar 01, 2007

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

Report · Mar 01, 2007

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

Report · Mar 01, 2007

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

Report · Mar 01, 2007

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

Report · Mar 01, 2007

.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

Report · Mar 01, 2007

.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

Report · Mar 01, 2007

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