Re: [PHP] MySQL Increment/Decrement
Ben It seems that you can just update the column with a update query like this, update table set field = field + 1 where some condition This might be the thing you need. Eric On 12/29/09, Ben Miller wrote: > > I hope this isn't a bone-head question - Is there a MySQL query that will > increment/decrement the value in an integer column with a single query - in > other words, I don't have to run a SELECT query to get the value, > add/subtract to/from the value, and then run an UPDATE query to store the > new value? > > > > Thanks in advance. > > > > Ben > >
Re: [PHP] not able to use mysql server
On 12/30/09, Jignesh Thummar wrote: > > It is problem with mysql client, it's not able to connect with mysql > server. > Either mysql server is not running or windows firewall is blocking to the > access of port 3306. Try to disable firewall. > > -Jignesh > > On Wed, Dec 30, 2009 at 11:41 AM, Sudhakar > wrote: > > > hi > > > > i am using windows vista and installed wamp server, initially i installed > > wamp and accessed phpmyadmin and while setting a password for the root i > > did > > a mistake and could not access phpmyadmin and also renamed some php files > > so > > i uninstalled wamp server and re installed > > > > now the problem i am facing is when i click on mysql.exe located at > > D:\wamp\bin\mysql\mysql5.1.36\bin i am getting the following error > > error 2003 cant connect to Mysql server on localhost 10061 > > > > in config.inc.php located at d:\wamp\apps\phpmyadmin3.2.0.1 i have the > > following > > $cfg['Servers'][$i]['host'] = 'localhost'; > > $cfg['Servers'][$i]['port'] = '3306'; > > $cfg['Servers'][$i]['auth_type'] = 'config'; > > $cfg['Servers'][$i]['user'] = 'root'; > > $cfg['Servers'][$i]['password'] = 'root'; > > $cfg['Servers'][$i]['AllowNoPassword'] = true; > > > > along with others. > > > > when i type http://localhost i am able to see the welcome screen of wamp > > and > > can click on phpinfo but when i click on phpmyadmin it takes a while to > > load > > and a blank screen appears > > > > also i have stopped all services and restarted all services from the > > wampserver icon in the system tray, normally the colors change from red > to > > yellow to white, the color is at yellow and does not change to white > > > > how do i solve the error error 2003 cant connect to Mysql server on > > localhost 10061 so that i can use phpmyadmin > > > > please advice. > > > > thanks. > > > Are there any error entries of mysql server on the event viewer ? Also, what is the starting state of mysql server on the services console ? You can found all tool on administrative folder on the start menu.
Re: [PHP] SMTP Local development to Send email in PHP; Windows Platform/ XP with no IIS
Hi, As I know that php did't setting user name and password. So, just install any smtp server with authenticaton set to "no authentication" Much list IIS smtp server. Eric, Regards, On 1/16/10, Andy Shellam wrote: > > Hi, > > > > > Also http://www.softstack.com/freesmtp.html which vikash mentioned works > > through outlook settings. > > > > Anyways the below will help- > > > > http://php.net/manual/en/ref.mail.php > > > > http://glob.com.au/sendmail/ > > > Personally, I always found hMailServer to be perfectly reliable as a relay > on Windows - just install it and SMTP to localhost - nothing more, nothing > less. > > Andy
Re: [PHP] Foreign Characters Break in MySQL
Hi, all,
I'am not able test it out now.
Did you tried to change the collation to utf8_bin from utf8_general_ci ?
Eric,
Regards,
On 1/22/10, Ryan Park wrote:
>
> Forgot to reply all.
>
> You can see that it's in the middle of the sql statement.
> It looks fine here but some how it breaks during the query.
>
> mysql_connect("localhost", "adminID", "password") or die(mysql_error());
> echo "Connected to MySQL";
>
> mysql_select_db("databasename") or die(mysql_error());
> echo "Connected to Database";
>
> $sql = "INSERT INTO xe_modules (module_srl, module, module_category_srl,
> layout_srl, menu_srl, site_srl, mid, skin, browser_title, description,
> is_default, content, open_rss, header_text, footer_text, regdate) VALUES
> ('135', 'bodex', '0', '53', '0', '0', 'free', 'xe_default', '자유게시판 ', '',
> 'N', '', 'Y', '', '', UNIX_TIMESTAMP());";
>
> mysql_query($sql) or die(mysql_error());
>
> mysql_close();
> ?>
>
> On 1/21/2010 5:19 PM, Jim Lucas wrote:
>
>> Ryan Park wrote:
>>
>>
>>> Hello I'm currently trying to use PHP to insert foreign characters into
>>> one of the mysql database tables.mysql_query() worked seamlessly, but when I
>>> check the inserted data on phpMyAdmin it shows the foreign characters in
>>> broken letters, like this ì‹œíŒ<- jibberish...The foreign characters show
>>> fine when I'm typing it out on my editor to code PHP, but it gets broken
>>> into unrecognizable symbols when put into mysql database columns.
>>> I tried to create the same thing this time through phpMyAdmin console and
>>> it worked great, the foreign characters showed correctly as they should.The
>>> column that I'm trying to put the foreign characters into is set as
>>> utf8_general_ci.I wish to use PHP to insert the data into the database
>>> because I'll be inserting massive amounts of them at once, so I just can't
>>> continue with this problem at hand.
>>> I'll greatly appreciate any help, thank you.
>>>
>>> _
>>> Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
>>> http://clk.atdmt.com/GBL/go/196390709/direct/01/
>>>
>>>
>> How about showing a little of the insert code. ie: how you are gathering
>> the
>> data, how you are preping the data, and the actual insert statement.
>>
>>
>>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
Re: [PHP] Upload file on IE8
On Tue, Jan 26, 2010 at 6:41 AM, Ernie Kemp wrote: > Good Day, > > > > I’m having an issue with IE8, when I go to load a file the > program is not filling the $_FILES['user_file']['type'] . > > When I display “echo "Start".$_FILES['pix']['type']."End";” > in IE8 I get “StartEnd” with nothing in between. I expected “image/pjpeg". > > > > On FireFox I get “image/jpeg". > > > > Any suggestions? > > Thanks, > > /Ernie > > > > I remember a setting from some elsewhere IE 8 was disable set file path > location > by default from the options dialog. Did you enabled this already ? If not, > you may give it a shoot to see than ! > > Hope this help > > > Eric, > Regards, > > > > > >
Re: [PHP] Speed of sending email .. can I put them in a queue rather than wait?
On Tue, Jan 26, 2010 at 12:02 PM, Angus Mann wrote: > Hi all. > > I'm currently using the phpmailer class from phpmailer.worxware.com to > send datatbase -populated emails to clients. > > At the moment I'm runninng PHP on Windows and using the built-in sendmail > equivalent packaged with XAMPP. It uses a remote SMTP that authenticates by > prior logging into a POP account. > > The number of emails sent is very small. Each one is only sent after a user > fills out a form and presses send. > > But there is a noticable lag of about 5 or sometimes 10 seconds after > pressing "send" before the user sees the "Mail sent" page. I presume the > reason for the lag is the time spent logging on and off a remote POP, then > SMTP server, transferring the data etc. > > It would be better if this happened in the background - that is, the user > could get on with doing his next task while the emails sat in a queue in the > backgorund, being lined up and sent without PHP waiting for the process to > finish. > > Can anybody recommend a good way of doing this? Is Mercury Mail going to > help me here? > > HI Angus, +1 I agree on what Paul already said . As in fact you were just to sent out low volume mails. Create a table and queue all pending mails in that then use windows scheduled tasks to perform the actual work if that is not a real-time mail. One thing to note, php on windows does't support specify username and password. But only hostname / IP Address and port only. I can not help much but just few more suggesions. Hope these help Eric, Regards,
Re: [PHP] Speed of sending email .. can I put them in a queue rather than wait?
Hi, all I'am doubted about installing a local mail server for just low volume mailing. May I ask all yours professional what do you think about it ? Thanks in advanced. Regards, Eric, > -- > Jim Lucas > NOC Manager > 541-323-9113 > BendTel, Inc. > http://www.bendtel.com > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP] Magic Methods not working
On Wed, Jan 27, 2010 at 11:04 AM, Paul M Foster wrote:
> I have a class which instantiates other classes, and has a magic method
> like this:
>
> function _get($classname)
> {
>return $this->instantiate($classname);
> }
>
> Obviously, the class also has an instantiate method which does what it
> says.
>
> Here's the problem: When I call the instantiate method, it works fine,
> like this:
>
> $db = $sc->instantiate('database');
>
> But when I do the following, it *doesn't* work:
>
> $db = $sc->database;
>
> In fact it does not call the instantiate() method; I've placed a print
> statement in the instantiate() method which fires at then end of the
> routine. That statement doesn't fire with the above code.
>
> The docs on magic methods are pretty slim, so I'm not sure what it is
> I'm missing. Can someone enlighten me?
>
> Paul
>
>
Paul,
I seem that you should missed the required underscore " _ "
It should the __get() but not _get().
Shall this help ?
And here is a quick test !!
[ [ [
class test
{
public $varname = 1000;
public function __get($name)
{
return $this->init($name);
}
public function init($name)
{
return new test();
}
}
$t = new test();
$db = $t->database;
if (is_object($db))
{
echo $db->varname;
}
] ] ]
Regards,
Eric,
--
> Paul M. Foster
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
Re: [PHP] Reliable way to identify Ogg types?
Hi Micheal, One of way for this should be by checking the header of the file with its OggS magic number. You may take a look at this page to see if it could help ! http://en.wikipedia.org/wiki/Ogg But I'am confused that the mime type should be enough what type of data its coming ! Are there are any more things you need to concern ? Thanks, Regards, Eric, On Wed, Jan 27, 2010 at 11:23 AM, Michael A. Peters wrote: > When I use fileinfo on an uploaded Ogg file, the mime it returns is > Application/Ogg which is almost useless. > > Is there a reliable php way, preferably without needing to execute shell > commands, to positively identify a file as Ogg Theora or Ogg Vorbis? > > Thanks for suggestions. > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP] Pointers For Newbies, Reminders For Oldies
On Wed, Jan 27, 2010 at 11:44 PM, Ashley Sheridan
wrote:
> On Wed, 2010-01-27 at 10:42 -0500, Paul M Foster wrote:
>
> > "... should be obvious - but are often overlooked - points within coding
> > practice that can cause the programmer to develop bad habits and bad
> > code." - Dan Brown
> >
> > Tip #1:
> >
> > Don't use count() in loops unless there are very few items to count and
> > performance doesn't matter, or the number will vary over the loop. That
> > is, don't do this:
> >
> > for ($i = 0; $i < count($items); $i++)
> >
> > Instead, do this:
> >
> > $number = count($items);
> > for ($i = 0; $i < $number; $i++)
> >
> > Reason: when you use the count() call at the top of the loop, it will
> > re-evaluate the number of items each time it's called, which usually
> > isn't necessary and adds time. Instead, work out the number of items
> > before going into the loop and simply refer to that for the number of
> > items in controlling the loop.
> >
> > Paul
> >
> > --
> > Paul M. Foster
> >
>
>
> What about using the right type of quotation marks for output:
>
> I use double quotes(") if I expect to output variables within the
> string, and single quotes when it's just a simple string.
>
> It's only a general rule of thumb and shouldn't be adhered to
> absolutely, but I remember a thread a while back that showed the speed
> differences between the two because of the extra parsing PHP does on
> double quoted strings.
>
>
That should be on the stackoverflow.com
It compare the string parsing with or without variables embeded
and the important of comma operator when ` echo ` data
use
echo 'something', 'other'
but not
echo 'something' . 'other'
Eric,
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
>
>
[PHP] exception throw from __autoload could not be catched on php 5.3.1
Hi php-dev pros,
I got an issue about catching exception throw from __autoload on php 5.3.1.
The manual state that exception throw from __autoload could be catched with
try.. catch statement same as the normal flow.
But I'can archive that even I have copied the same sample code from the
manual.
Here are the code segment.
[[[
function __autoload($name) {
echo "Want to load $name.\n";
throw new Exception("Unable to load $name.");
}
try {
$obj = new NonLoadableClass();
} catch (Exception $e) {
echo $e->getMessage(), "\n";
}
]]]
Are there anyone experienced this or not ?
Thanks in advance !
Regards,
Eric,
Re: [PHP] exception throw from __autoload could not be catched on php 5.3.1
On Fri, Jan 29, 2010 at 1:19 PM, Ashley Sheridan
wrote:
> On Fri, 2010-01-29 at 13:02 +0800, Eric Lee wrote:
>
> Hi php-dev pros,
>
> I got an issue about catching exception throw from __autoload on php 5.3.1.
>
> The manual state that exception throw from __autoload could be catched with
> try.. catch statement same as the normal flow.
>
> But I'can archive that even I have copied the same sample code from the
> manual.
>
> Here are the code segment.
>
> [[[
> function __autoload($name) {
> echo "Want to load $name.\n";
> throw new Exception("Unable to load $name.");
> }
>
> try {
> $obj = new NonLoadableClass();
> } catch (Exception $e) {
> echo $e->getMessage(), "\n";
> }
>
> ]]]
>
> Are there anyone experienced this or not ?
>
> Thanks in advance !
>
> Regards,
> Eric,
>
>
> Sorry, ignore that, I see you're running 5.3.1, which should be fine for
> running the example. I do notice that you've got [[[ and ]]] in-place of
> , was that intentional?
>
Sorry for that ! I'am too lazy !!
And that's just for easy looking the code.
I actually use this,
getMessage(), "\n";
}
?>
Regards,
Eric,
>
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
>
>
Re: [PHP] exception throw from __autoload could not be catched on php 5.3.1
Hi all and thanks for Ryan,
I apologize ! !
I have missed out the small class_exists call before it.
Thanks.
Regards,
Eric,
2010/1/29 Ryan
> 于 2010-1-29 13:19, Ashley Sheridan 写道:
> > On Fri, 2010-01-29 at 13:02 +0800, Eric Lee wrote:
> >
> >
> >> Hi php-dev pros,
> >>
> >> I got an issue about catching exception throw from __autoload on php
> 5.3.1.
> >>
> >> The manual state that exception throw from __autoload could be catched
> with
> >> try.. catch statement same as the normal flow.
> >>
> >> But I'can archive that even I have copied the same sample code from the
> >> manual.
> >>
> >> Here are the code segment.
> >>
> >> [[[
> >> function __autoload($name) {
> >> echo "Want to load $name.\n";
> >> throw new Exception("Unable to load $name.");
> >> }
> >>
> >> try {
> >> $obj = new NonLoadableClass();
> >> } catch (Exception $e) {
> >> echo $e->getMessage(), "\n";
> >> }
> >>
> >> ]]]
> >>
> >> Are there anyone experienced this or not ?
> >>
> >> Thanks in advance !
> >>
> >> Regards,
> >> Eric,
> >>
> >
> > Sorry, ignore that, I see you're running 5.3.1, which should be fine for
> > running the example. I do notice that you've got [[[ and ]]] in-place of
> > , was that intentional?
> >
> > Thanks,
> > Ash
> > http://www.ashleysheridan.co.uk
> >
> >
> >
> >
> Do you have any other autoload implamentaions? try testing that code
> segment in a single php file and dump phpinfo() to make sure you are on
> php 5.3.1
>
Re: [PHP] Re: how do I use php://memory?
On Sat, Jan 30, 2010 at 9:00 AM, Shawn McKenzie wrote:
> Mari Masuda wrote:
>
> > Has anyone ever successfully used php://memory before? If so, what
> > can I do to use it in my code? Thank you.
>
> No, but I was intrigued to try it, so I tested this:
>
> $text = 'Some text.';
> file_put_contents('php://memory', $text);
> echo file_get_contents('php://memory');
>
> And it returned nothing. The docs suck on this and it apparently
> doesn't work. I see others use it with fopen(), but there is no mention
> of which file functions it works with and which it doesn't.
>
>
Shawn
I did a sample test from the manual with fopen like this,
console output
F:\wc\trunk>php -f m.php
line 1
Regards,
Eric,
--
> 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] File Upload
On Sat, Jan 30, 2010 at 7:27 PM, Ali Reza Sajedi wrote: > Hello, > > When uploading a file the variable $_FILES['userfile']['tmp_name'] is not > set and when debugging I get the following error although /tmp folder exists > and the permissions are set to 777: > > $_FILES['userfile']['error'] = 6 > > which says > > UPLOAD_ERR_NO_TMP_DIR > Value: 6; Missing a temporary folder. Introduced in PHP 4.3.10 and PHP > 5.0.3. > > Has anyone encountered such a problem or has a clue as to what the cause > could be? > > It might be the upload_tmp_dir no pointing to the right dir ! What is the current of it ? Regards, Eric, Thank you. > > Kind regards > > Ali > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP] stream_select() not working on regular files?
On Thu, Feb 4, 2010 at 9:20 AM, Dennis J. wrote:
> On 02/04/2010 02:03 AM, Ashley Sheridan wrote:
>
>> On Thu, 2010-02-04 at 01:41 +0100, Dennis J. wrote:
>>
>>> Hi,
>>> I'm trying to implement something similar to"tail -f" in php but I'm
>>> running into a problem.
>>> The issue occurs when I've reached the end of the file. From here I
>>> basically have to loop until new lines get appended to the file but I
>>> would
>>> like to respond immediately when then happens. There are three options
>>> that
>>> I can see:
>>>
>>> 1. Busy-loop
>>> pro: I can process new lines immediately
>>> contra: Excessivley CPU intensive => not a real option
>>>
>>> 2. add a sleep(1) to the loop
>>> pro: No longer kills the CPU
>>> contra: I might get a 1 second delay until I can process new lines
>>>
>>> 3. stream_select(array($fh),null,null,1)
>>> pro: sleeps for one second but returns earlier if new data arrives
>>> contra: doesn't seem to work in files?
>>>
>>> Method 3 is the preferable one but doesn't seem to work:
>>>
>>> $fh = fopen("testfile","r");
>>> $r = array($fh);
>>> while( ($n = stream_select($r,$w=null,$e=null,1)) == 1 ) {
>>> echo fgets($fh);
>>> }
>>>
>>> This program will loop forever because stream_select() will always return
>>> 1
>>> even at the end of the file.
>>>
>>> Is there any other way to accomplish this?
>>>
>>> Regards,
>>>Dennis
>>>
>>>
>> I thought that once it reached the end of the file, it will return a 0
>> indicating no new activity?
>>
>
> That's what I thought too but apparently that is not the case.
>
>
> Although, surely you want the loop to continue forever, so that new
>> entries added to the end of the file are shown as soon as they appear.
>>
>
> Yes the loop is supposed to continue forever in the final version. In fact
> I what I'm trying to get at is a "tail -F" which means I will repeatedly
> reopen the file to check if it has been replaced by a new one. I just
> simplified the problem above to get rid of all the additional complexity and
> concentrate on the specific problem I have.
>
> My expectation was that once the end of the file is reached (i.e. fgets()
> has consumed all lines) stream_select() should wait for 1 second (in the
> above example) and if nothing happens with the file in that second it should
> return 0. But that doesn't happen.
>
>
>
Dennis
I have just been bulit a simple test script.
It works for me with a feof call if the stream was at end of file.
But I'am not sure that is that what you want !
Regards,
> Dennis
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
Re: [PHP] syntax error in class
2010/2/11 Jim Lucas > Dasn wrote: > >> I got the syntax error. Why? >> > > only constant literal is allowed. You can think that it is static complie but not on runtime . Regards, Eric, > Read the first paragraph on the following page. > > http://us3.php.net/manual/en/language.oop5.properties.php > > [quote] > ... This declaration may include an initialization, but this > initialization must be a constant value--that is, it must be able to be > evaluated at compile time and must not depend on run-time information in > order to be evaluated. > [/quote] > > Basically, anything that requires the concatenation of string parts or > usage of variables is not allowed. > > -- > Jim Lucas > > "Some men are born to greatness, some achieve greatness, > and some have greatness thrust upon them." > > Twelfth Night, Act II, Scene V >by William Shakespeare > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP] How to secure this
On Sat, Feb 13, 2010 at 7:33 AM, Ryan Sun wrote: > In that case, referer is for authentication, and id is for authorization, I > think > > On Fri, Feb 12, 2010 at 6:23 PM, Ashley Sheridan > wrote: > > > On Fri, 2010-02-12 at 18:25 -0500, Ryan Sun wrote: > > > > authenticate by remote domain name or remote ip > > > > $_SERVER['HTTP_REFERER'] > > > > then your clients will not have to put their username/password in clear > texthttp://www.mydomain.com?h=300&w=250 > > and you will just check if you have their domain on your list > > > > I'm not sure if there is better one but > > " 'HTTP_REFERER' > > The address of the page (if any) which referred the user agent to > > the current page. This is set by the user agent. Not all user agents > > will set this, and some provide the ability to modify HTTP_REFERER as > > a feature. In short, it cannot really be trusted. " > > > > > > On Fri, Feb 12, 2010 at 4:26 PM, Robert Cummings > wrote: > > > Ashley Sheridan wrote: > > >> > > >> On Fri, 2010-02-12 at 16:12 -0500, Robert Cummings wrote: > > >> > > >>> John Allsopp wrote: > > > > Hi everyone > > > > There may be blinding bits of total ignorance in this so don't > ignore > > the obvious. > > > > This is a security question, but a sentence of background: I'm > writing > > software for a mapping/location website and I want to be able to > provide > > something others can plug into their website that would display > their map. > > > > So I'm providing a URL like > > http://www.mydomain.com?h=300&w=250&username=name&password=password > > > > The idea is they can define their own height and width and it plugs > in > > as an iframe. > > > > That takes the username and password and throws it over web services > to > > get back the data from which we can create the map. > > > > My question (and it might be the wrong question) is how can I not > give > > away the password to all and sundry yet still provide a > self-contained URL? > How about RESTful like checking ? It is much like what Rob said already. but join all params by order and md5 it altogether Regards, Eric, > > >>> > > >>> MD5() (or SHA()) hash the information and supply that along with the > > >>> settings. Then you know it was generated by your site. So you can do > the > > >>> following: > > >>> > > >>> > >>> > > >>> $height = 300; > > >>> $width = 250; > > >>> $username = 'username'; > > >>> $key = md5( "SECRET_SALT-$heigh-$width-$username" ); > > >>> > > >>> $url = > > >>> " > http://www.mydomain.com?h=$height&w=$width&username=$username&key=$key";; > > >>> > > >>> ?> > > >>> > > >>> Then when you get this URL via the iframe, you re-compute the > expected > > >>> key and then compare it against the given key. Since only you know > the > > >>> SECRET_SALT value then nobody should be able to forge the key. > > >>> > > >>> Cheers, > > >>> Rob. > > >>> -- > > >>> http://www.interjinn.com > > >>> Application and Templating Framework for PHP > > >>> > > >> > > >> > > >> What about requiring them to sign in the first time to use your > service, > > >> and then give them a unique id which i tied to their details. You > could > > >> then get them to pass across this id in the url. You could link their > > >> account maybe to some sorts of limits with regards to what they can > > >> access maybe? > > > > > > Presumably they ARE logged in when you create this URL for them... > otherwise > > > someone else could generate it :) > > > > > > Cheers, > > > Rob. > > > -- > > > http://www.interjinn.com > > > Application and Templating Framework for PHP > > > > > > -- > > > PHP General Mailing List (http://www.php.net/) > > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > > > > > > I think Google does both the referrer check coupled with an id passed in > > the URL. At least, this is what it did the last time I embedded one of > their > > maps. > > > > > > Thanks, > > Ash > > http://www.ashleysheridan.co.uk > > > > > > >
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
On Sat, Feb 13, 2010 at 2:07 PM, 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.
>
> I'm using mysql atm, so i thought "stored procedures!"..
> But alas, mysql docs are very basic.
>
> I got the gist of how to setup a stored proc, but how to retrieve a
> list of auto_increment ids still eludes me; last_insert_id() only
> returns for the last row i believe.
> So building an INSERT (...) VALUES (...),(...) at the php end, is
> probably not the way to go then.
>
> But the mysql docs don't show how to pass an array to a stored
> procedure, so i can't just have the stored proc loop over an array,
> insert per row, retrieve last_insert_id() into temp table, and return
> the temp table contents for a list of auto_increment ids for inserted
> rows.
>
> Any clues are greatly appreciated..
> I'm looking for the most sql server independent way to do this.
>
>
Rene
I have not been worked with mysql multi-insert before.
But just did a simple test on my mysql 5.0 copy.
I assume that you are using MyISAM table and will lock its read, writel
when inserting data.
When multi-insert was done, and did a select last_insert_id(). I saw that
only
the first inserted id was returned. Please take a look the following steps:
mysql> select * from temp;
Empty set (0.00 sec)
mysql> insert into temp (firstname, price) values ('dd', 10), ('cc', 3),
('bb', 99);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select last_insert_id();
+--+
| last_insert_id() |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql> insert into temp (firstname, price) values ('dd', 10), ('cc', 3),
('bb', 99);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select last_insert_id();
+--+
| last_insert_id() |
+--+
|4 |
+--+
1 row in set (0.00 sec)
So, let's say three records was inserted, and the first inserted id was 1.
You get id from 1 to 3.
! This will not work on transaction-based insert !
Just a thought and tested on mysql but not on php.
Regards,
Eric
> --
> 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 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 ?
Thanks !
Regards,
Eric
> --
> 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 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 > <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
> >
> >
>
>
