Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
On Sat, Feb 13, 2010 at 3:46 PM, Joseph Thayne webad...@thaynefam.org 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?
On Sunday 14 February 2010 03:15:16 am Rene Veerman wrote: On Sat, Feb 13, 2010 at 3:46 PM, Joseph Thayne webad...@thaynefam.org 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?
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?
On Sat, Feb 13, 2010 at 7:41 PM, Jochem Maas joc...@iamjochem.com wrote:
Op 2/13/10 11:36 AM, Eric Lee schreef:
On Sat, Feb 13, 2010 at 6:55 PM, Jochem Maas joc...@iamjochem.com
mailto:joc...@iamjochem.com 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?
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 b3n...@googlemail.com
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
Michael A. Peters wrote: Rene Veerman wrote: On Sat, Feb 13, 2010 at 9:05 AM, Michael A. Peters mpet...@mac.com 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
Shawn McKenzie wrote:
Michael A. Peters wrote:
Rene Veerman wrote:
On Sat, Feb 13, 2010 at 9:05 AM, Michael A. Peters mpet...@mac.com
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:
?php
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 stupid)
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
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 mpet...@mac.com
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:
?php
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 stupid)
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
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, font 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 head 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
[PHP] Quick research
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
Shawn McKenzie wrote: Michael A. Peters wrote: Rene Veerman wrote: On Sat, Feb 13, 2010 at 9:05 AM, Michael A. Peters mpet...@mac.com 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?
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
