Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?

[Rene Veerman]

On Sat, Feb 13, 2010 at 3:46 PM, Joseph Thayne  wrote:
> In order to make this as "sql server independent" as possible, the first
> thing you need to do is not use extended inserts as that is a MySQL
> capability.  If you are insistent on using the extended inserts, then look
> at the mysql_info() function.  That will return the number of rows inserted,
> etc. on the last query.
>

But as previous posters had pointed out (thanks) i can't see which rows failed.
As i'm dealing with 3rd-party data, that's an issue.

I also didn't know it was mysql-specific, that multi-insert..

And i tried looking up the sql-standard docs, only to find that they
cost over 200 euro per
part (14 parts).
I've sent angry emails to ansi.org and iso.org (commercial lamers
operating under .org, yuck), about how cool a business model that
charges a percentage of profits per implementation would be, instead
of charging high prices up-front for a potentially bad/complicated
piece of spec.

But back to the problem at hand; it looks like i'll have to forget
about using 100s of threads for my newsscraper at the same time, and
settle for a few dozen instead.
Then i can just do single inserts (per hit) and retrieve the last_insert_id().

One question remains: it is probably not (concurrently-)safe to do a
sql-insert from php and then a last_insert_id() also from php..?
I still have to build a stored procedure to do-the-inserting and
return the last_insert_id()?

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?

[Larry Garfield]

On Sunday 14 February 2010 03:15:16 am Rene Veerman wrote:
> On Sat, Feb 13, 2010 at 3:46 PM, Joseph Thayne  
wrote:
> > In order to make this as "sql server independent" as possible, the first
> > thing you need to do is not use extended inserts as that is a MySQL
> > capability.  If you are insistent on using the extended inserts, then
> > look at the mysql_info() function.  That will return the number of rows
> > inserted, etc. on the last query.
> 
> But as previous posters had pointed out (thanks) i can't see which rows
>  failed. As i'm dealing with 3rd-party data, that's an issue.
> 
> I also didn't know it was mysql-specific, that multi-insert..
> 
> And i tried looking up the sql-standard docs, only to find that they
> cost over 200 euro per
> part (14 parts).
> I've sent angry emails to ansi.org and iso.org (commercial lamers
> operating under .org, yuck), about how cool a business model that
> charges a percentage of profits per implementation would be, instead
> of charging high prices up-front for a potentially bad/complicated
> piece of spec.
> 
> But back to the problem at hand; it looks like i'll have to forget
> about using 100s of threads for my newsscraper at the same time, and
> settle for a few dozen instead.
> Then i can just do single inserts (per hit) and retrieve the
>  last_insert_id().
> 
> One question remains: it is probably not (concurrently-)safe to do a
> sql-insert from php and then a last_insert_id() also from php..?
> I still have to build a stored procedure to do-the-inserting and
> return the last_insert_id()?

That's perfectly safe to do as long as it's within the same PHP request. 
(Well, the same DB connection, really, which is 99% of the time the same 
thing.)  last_insert_id() is connection-specific.

I believe (it's been a while since I checked) the MySQL documentation says 
that last_insert_id() with a multi-insert statement is not reliable and you 
shouldn't rely on it having a worthwhile meaning anyway.  Or at least it said 
something that made me conclude that it's safest to assume it's unreliable for 
a multi-insert statement.

If you're concerned about performance of that many bulk writes, there's 3 
things you can do to help:

1) Use InnoDB.  It uses row-level locking so lots of writes doesn't lock your 
whole table as in MyISAM tables.

2) Disable indexes on the table in question before running your bulk insert, 
then re-enable them.  That's considerably faster than rebuilding the index 
after each and every insert as they only need to be rebuilt once.

3) If you're on InnoDB, using transactions can sometimes give you a 
performance boost because the writes hit disk all at once when you commit.  
There may be other side effects and trade offs here, though, so take with a 
grain of salt.

--Larry Garfield

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Inserting Associative array values into a MySQL INSERT statement?

[Ben Stones]

Hi,

I want to be able to create a function that acts as an insert mysql function
that accepts specific parameters for the fields and the values I want to
insert into those respective fields and I know I'll need to use associative
arrays to complete this task when passing values to the function, but I'm
not sure how to pass multiple values in an array through an insert
statement? Any help greatly appreciated!

Thanks.

Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?

[Eric Lee]

On Sat, Feb 13, 2010 at 7:41 PM, Jochem Maas  wrote:

> Op 2/13/10 11:36 AM, Eric Lee schreef:
> >
> >
> > On Sat, Feb 13, 2010 at 6:55 PM, Jochem Maas  > > wrote:
> >
> > Op 2/13/10 10:08 AM, Lester Caine schreef:
> > > Rene Veerman wrote:
> > >> Hi.
> > >>
> > >> I'm looking for the most efficient way to insert several records
> and
> > >> retrieve the auto_increment values for the inserted rows, while
> > >> avoiding crippling concurrency problems caused by multiple php
> > threads
> > >> doing this on the same table at potentially the same time.
> > >
> > >> Any clues are greatly appreciated..
> > >> I'm looking for the most sql server independent way to do this.
> > >
> > > Rene
> > > The 'correct' way of doing this is to use a 'sequence' which is
> > > something introduced in newer versions of the SQL standard.
> > > Firebird(Interbase) has had 'generators' since the early days (20+
> > > years) and these provide a unique number which can then be
> > inserted into
> > > the table.
> > >
> > > ADOdb emulates sequences in MySQL by creating a separate table for
> the
> > > insert value, so you can get the next value and work with it,
> without
> > > any worries. The only 'problem' is in situations were an insert is
> > > rolled back, a number is lost, but that is ACTUALLY the correct
> > result,
> > > since there is no way of knowing that a previous insert WILL
> > commit when
> > > several people are adding records in parallel.
> >
> > this is all true and correct ...
> >
> > but that doesn't answer the problem. how do you get the IDs of all
> > the records
> > that we're actually inserted in a multi-insert statement, even if
> > you generate the
> > IDs beforehand you have to check them to see if any one of the set
> > INSERT VALUEs failed.
> >
> > @Rene:
> >
> > I don't think there is a really simple way of doing this in a RDBMS
> > agnostic
> > way, each RDBMS has it's own implementation - although many are
> > alike ... and MySQL is
> > pretty much the odd one out in that respect.
> >
> > it might require a reevaluation of the problem, to either determine
> > that inserting
> > several records at once is not actually important in terms of
> > performance (this would depend
> > on how critical the speed is to you and exactly how many records
> > you're likely to be inserting
> > in a given run) and whether you can rework the logic to do away with
> > the requirement to
> > get at the id's of the newly inserted records ... possibly by
> > indentifying a unique
> > indentifier in the data that you already have.
> >
> > one way to get round the issue might be to use a generated GUID and
> > have an extra field which
> > you populate with that value for all records inserted with a single
> > query, as such it could
> > function as kind of transaction indentifier which you could use to
> > retrieve the newly
> > inserted id's with one extra query:
> >
> >$sql = "SELECT id FROM foo WHERE insert_id = '{$insertGUID}'";
> >
> > ... just an idea.
> >
> > >
> >
> >
> >
> > Hi
> >
> > I would like to learn more correct  way from both of you.
> > May I ask what is a sequences ?
>
> it an RDBMS feature that offers a race-condition free method of
> retrieving a new unique identifier for a record you wish to enter,
> the firebird RDBMS that Lester mentions refers to this as 'generators'.
>
> to learn more I would suggest STW:
>
>http://lmgtfy.com/?q=sql+sequence
>
>
 Jochem


Thanks,

Regards,
Eric

> >
> >
> > Thanks !
> >
> >
> > Regards,
> > Eric
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
>

Re: [PHP] Inserting Associative array values into a MySQL INSERT statement?

[TG]

I built a multi-purpose query builder for INSERT and UPDATE statements.  
You send it certain and it sends back the SQL, minus the WHERE clause for 
the UPDATE SQL.  I thought it would be dangerous to leave it without any 
WHERE clause because if you forgot to add one, you'd end up doing that 
UPDATE on every row in the table which is generally not good.  I still 
haven't decided how I want to handle the WHERE clause thing, but for now 
there's a placeholder that I do a str_replace() on after I call the 
function.

Parameters:

$table - name of table that's being inserted into or updated
$arr - associative array of values.  key is the column/field name and value 
is the value it'll be set to.
$dateupdates - an array with a list of values corresponding to the date 
fields I want updated to NOW()
$type - whether it's an insert or an update


The dbclean() function is one I wrote so I don't have to go through all my 
code changing mysql_real_escape_string() to something else or more 
specific if I need to updated how I clean the data going into the 
database and/or used in queries.  In the past, I've also passed a "type" 
variable to this as well, indicating the type of data and then doing 
specific things depending on the type, but I'm rebuilding my common 
functions from scratch and haven't gotten to that part yet.

Here ya go, in case it helps:

function dbBuildIUQuery($table = '', $arr = array(), $dateupdates = 
array(), $type = '') {

$query = '';

switch ($type) {
case 'insert':
$query = "INSERT INTO " . dbclean($table) . " (";
$queryvalues = ") VALUES (";

$arrkeys = array_keys($arr);
$arrvals = array_values($arr);

foreach($arrkeys as $key => $val) {
$arrkeys[$key] = "`" . dbclean($val) . "`";
}
foreach($arrvals as $key => $val) {
$arrvals[$key] = "'" . dbclean($val) . "'";
}

foreach ($dateupdates as $key) {
$arrkeys[] = '`' . dbclean($key) . '`';
$arrvals[] = 'NOW()';
}

$query   .= implode(',', $arrkeys);
$queryvalues .= implode(',', $arrvals) . ");";

$query .= $queryvalues;

break;
case 'update':

$query = "UPDATE " . dbclean($table) . " SET ";
$queryvalues = array();
foreach ($arr as $key => $val) {
$queryvalues[] = " `" . dbclean($key) . "` = '" . 
dbclean($val) . "'";
}
$query .= implode(',', $queryvalues) . ' WHERE {whereclause};'; 
 // Added {whereclause} so if we forget to add one, the query fails 
without setting all rows to these values

break;
default:
break;
}

return $query;
}

-TG

- Original Message -
From: Ben Stones 
To: php-general@lists.php.net
Date: Sun, 14 Feb 2010 13:18:06 +
Subject: [PHP] Inserting Associative array values into a MySQL INSERT 
statement?

> Hi,
> 
> I want to be able to create a function that acts as an insert mysql 
function
> that accepts specific parameters for the fields and the values I want to
> insert into those respective fields and I know I'll need to use 
associative
> arrays to complete this task when passing values to the function, but I'm
> not sure how to pass multiple values in an array through an insert
> statement? Any help greatly appreciated!
> 
> Thanks.
> 
> 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] optional object arguments to a function

[Shawn McKenzie]

Michael A. Peters wrote:
> Rene Veerman wrote:
>> On Sat, Feb 13, 2010 at 9:05 AM, Michael A. Peters 
>> wrote:
>>> How do I specify a default null object, or otherwise make the argument
>>> argument optional?
>>>
>> To my knowledge: can't be done.
>>
>> But you can check any args through the func_get_arg*() functions, then
>> per-parameter push 'm through a check function that checks if their
>> primary properties are set.
>> It's equivalent to checking for null ( / bad) objects.
>>
> 
> Thank you to everybody. I think I will see how far I can get with
> func_get_arg - it may solve the problem.
> 
> The other hackish solution I thought of is to put the object arguments
> into a key/value array and pass the array as a single argument to the
> function. That way I can check for the key and if the key is set, grab
> the object associated with it.

Maybe I mis-read your post, but what's wrong with Jochem's method.
That's what I was going to propose.

-- 
Thanks!
-Shawn
http://www.spidean.com

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] optional object arguments to a function

[Nathan Rixham]

Shawn McKenzie wrote:
> Michael A. Peters wrote:
>> Rene Veerman wrote:
>>> On Sat, Feb 13, 2010 at 9:05 AM, Michael A. Peters 
>>> wrote:
 How do I specify a default null object, or otherwise make the argument
 argument optional?

>>> To my knowledge: can't be done.
>>>
>>> But you can check any args through the func_get_arg*() functions, then
>>> per-parameter push 'm through a check function that checks if their
>>> primary properties are set.
>>> It's equivalent to checking for null ( / bad) objects.
>>>
>> Thank you to everybody. I think I will see how far I can get with
>> func_get_arg - it may solve the problem.
>>
>> The other hackish solution I thought of is to put the object arguments
>> into a key/value array and pass the array as a single argument to the
>> function. That way I can check for the key and if the key is set, grab
>> the object associated with it.
> 
> Maybe I mis-read your post, but what's wrong with Jochem's method.
> That's what I was going to propose.
> 

This is a problem with php; you can't do the following (since object
isn't a class):
  function test( object $o = null )

so normally you'd do:
  function test( stdClass $o = null )

but this only works for stdClass - (object)"something" and *not*
instances of classes:

  

will fail because Foo is not an instance of stdClass

in short there is no way (in PHP) to type hint that something should be
an object of any class.

thus you have two options to work around this; option 1:

check yourself:
  function test( $o = null ) {
if( $o !== null && !is_object($o) ) {
   throw new InvalidArgumentException( '$o must be an object' );
}
  }

ensure you always only use instances of classes and not just
"objects/stdClass" (or make everything extend stdClass )

back to the main question - How do I specify a default null object -
like this:

function foo($a='',$b='',$c=false, $o=null) {
  if( $o !== null && !is_object($o) ) {
throw new InvalidArgumentException( '$o must be an object' );
  }
  // in the same way a you'd do
  if( !is_string($a) ) {
throw new InvalidArgumentException( '$a must be a string' );
  }
}

side note: if you're finding you may need an unknown number of arguments
then other than refactoring all your design to handle one argument at a
time to avoid cross cutting concerns, then you're stuck with
func_get_arg and checking each argument as you go; not strict but if it
works and it's fast..

feel like I've just typed "blah blah blah" for the last 10 minutes, ahh
well ho hum!

regards :)

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] optional object arguments to a function

[Shawn McKenzie]

Nathan Rixham wrote:
> Shawn McKenzie wrote:
>> Michael A. Peters wrote:
>>> Rene Veerman wrote:
 On Sat, Feb 13, 2010 at 9:05 AM, Michael A. Peters 
 wrote:
> How do I specify a default null object, or otherwise make the argument
> argument optional?
>
 To my knowledge: can't be done.

 But you can check any args through the func_get_arg*() functions, then
 per-parameter push 'm through a check function that checks if their
 primary properties are set.
 It's equivalent to checking for null ( / bad) objects.

>>> Thank you to everybody. I think I will see how far I can get with
>>> func_get_arg - it may solve the problem.
>>>
>>> The other hackish solution I thought of is to put the object arguments
>>> into a key/value array and pass the array as a single argument to the
>>> function. That way I can check for the key and if the key is set, grab
>>> the object associated with it.
>> Maybe I mis-read your post, but what's wrong with Jochem's method.
>> That's what I was going to propose.
>>
> 
> This is a problem with php; you can't do the following (since object
> isn't a class):
>   function test( object $o = null )
> 
> so normally you'd do:
>   function test( stdClass $o = null )
> 
> but this only works for stdClass - (object)"something" and *not*
> instances of classes:
> 
>  class Foo {}
>   $o = new Foo();
>   test( $foo );
>   ?>
> 
> will fail because Foo is not an instance of stdClass
> 
> in short there is no way (in PHP) to type hint that something should be
> an object of any class.
> 
> thus you have two options to work around this; option 1:
> 
> check yourself:
>   function test( $o = null ) {
> if( $o !== null && !is_object($o) ) {
>throw new InvalidArgumentException( '$o must be an object' );
> }
>   }
> 
> ensure you always only use instances of classes and not just
> "objects/stdClass" (or make everything extend stdClass )
> 
> back to the main question - How do I specify a default null object -
> like this:
> 
> function foo($a='',$b='',$c=false, $o=null) {
>   if( $o !== null && !is_object($o) ) {
> throw new InvalidArgumentException( '$o must be an object' );
>   }
>   // in the same way a you'd do
>   if( !is_string($a) ) {
> throw new InvalidArgumentException( '$a must be a string' );
>   }
> }
> 
> side note: if you're finding you may need an unknown number of arguments
> then other than refactoring all your design to handle one argument at a
> time to avoid cross cutting concerns, then you're stuck with
> func_get_arg and checking each argument as you go; not strict but if it
> works and it's fast..
> 
> feel like I've just typed "blah blah blah" for the last 10 minutes, ahh
> well ho hum!
> 
> regards :)

I guess I missed the part where he wasn't going to know what class the
object was from.  I would think you would normally know.

function foo($a = '', $b = '', $c = false, myClass $o = null)


-- 
Thanks!
-Shawn
http://www.spidean.com

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] HTML & plain text in Outlook 2007

[Skip Evans]

Ashley Sheridan wrote:
That last reason could be why your email is failing! HTML email is the 
one place where it is actually better to code "the old way" with tables 
for markup,  tags, and very little (if any) CSS. If you do use any 
CSS, it's best left inline as well, as some email clients strip out 
anything within the  tags of your email.


Yes, that's exactly what I took away from the conversation. 
HTML emails should be coded using "the old way".


Skip


--

Skip Evans
PenguinSites.com, LLC
503 S Baldwin St, #1
Madison WI 53703
608.250.2720
http://penguinsites.com

Those of you who believe in
telekinesis, raise my hand.
 -- Kurt Vonnegut

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Re: confirm subscribe to php-general@lists.php.net

[Developer Team - MDS Lab]

[PHP] Quick research

[Nathan Rixham]

Hi All,

If you have 2 seconds could you answer the following 1 (one) question
please  http://poll.fm/1lr8t

Many thanks in advance if you answer, yes it is PHP related (ultimately)
& need to get a good cross section of exposure.

Nathan

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] optional object arguments to a function

[Michael A. Peters]

Shawn McKenzie wrote:

Michael A. Peters wrote:

Rene Veerman wrote:

On Sat, Feb 13, 2010 at 9:05 AM, Michael A. Peters 
wrote:

How do I specify a default null object, or otherwise make the argument
argument optional?


To my knowledge: can't be done.

But you can check any args through the func_get_arg*() functions, then
per-parameter push 'm through a check function that checks if their
primary properties are set.
It's equivalent to checking for null ( / bad) objects.


Thank you to everybody. I think I will see how far I can get with
func_get_arg - it may solve the problem.

The other hackish solution I thought of is to put the object arguments
into a key/value array and pass the array as a single argument to the
function. That way I can check for the key and if the key is set, grab
the object associated with it.


Maybe I mis-read your post, but what's wrong with Jochem's method.
That's what I was going to propose.



Possibly nothing - but passing an array with keys I think makes it 
easier to determine what the object (a DOM node) corresponds with by 
just looking at the key (IE for a single integral there can be lower 
limit, upper limit, etc.)


Maybe after implementing it that way and actually getting things 
working, I can look into passing the dom objects as actual arguments 
instead of wrapping them in array.


But some existing functions like tidy take some of their optional 
arguments in a key->value array and it is now in php core so it isn't 
unprecedented to use a key->value array.


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP] Re: Report generators: experience, recommendations?

[Jonathan Sachs]

On Sat, 13 Feb 2010 20:01:35 -0500, n...@ridersite.org (Al) wrote:

>> I'm looking for a report generator which will be used to create
>> management reports for my client from a MySQL database
>>
>> Has anyone had experience with report generators that meet these
>> criteria? What would you recommend; what would you stay away from?
>
>Try Source Forge.

Al: I appreciate your effort to be helpful, but if you review my
original post, you'll find that the question you answered is not the
one I asked.

I hope that others who have used one or more report generators will
share their thoughts.

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php