[PHP] Re: php/mysql Query Question.
ad...@buskirkgraphics.com wrote: Before most of you go on a rampage of how to please read below... As most of you already know when using MySQL from the shell you can write your queries in html format in an out file. Example: shellmysql -uyourmom -plovesme --html This now will return all results in an html format from all queries. Now I could “tee” this to a file and save the results returned if I so choose to save the result of the display . Let’s say I want to be lazy and write a php MySQL query to do the same so that any result I queried for would return the html results in a table without actually writing the table tags in the results. Is there a mysql_connect or select_db or mysql_query tag option to do that since mysql can display it from the shell? I think you'll find that the HTML output is a function of the mysql command line program (I tend to use PostgreSQL, where 'psql' is a similar program) so you can only access that functionality by calling the command line. I suspect that, since PHP is a HTML processing language (originally), the creators of the mysql_ functions figured that the user could sort out making HTML from the data returned... It's should be a simple operation to write a wrapper function to put HTML around the results. There might even be a PEAR extension or PHPClasses class to do it (I haven't looked yet) -- Peter Ford phone: 01580 89 Developer fax: 01580 893399 Justcroft International Ltd., Staplehurst, Kent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Jim Lucas wrote: [EMAIL PROTECTED] wrote: Ok, so just that I am clear, you are SELECTing and pulling all the data that you are submitting in the above INSERT statement from the DB initially, then you are only modifying the confirm_number value and then re- submitting all the values, as they originally were, Well, actually when all is said and done, a new record will be created with new information (Name, phone, email, etc) and the confirm_number is the previous+1 Seems like a perfect candidate for an auto-inc field, though mysql doesn't let you have multiple in the same table (afaik). # Now prepare your statement $SQL = SET @confirm_number=(SELECT (MAX(confirm_number)+1) FROM `contacts`); INSERT INTO `contacts` ( `first_name`, `last_name`, `email`, `phn_number`, `address`, `city`, `state`, `zip`, `dates`, `comments`, `confirm_number` ) VALUES ( '{$FirstName}', '{$LastName}', '{$Email}', '{$Phone}', '{$Address}', '{$City}', '{$selected_state}', '{$Zip}', '{$newdate}', '{$Comments}', @confirm_number ) SELECT @confirm_number AS confirm_number; ; You do have a race condition, you can end up with 2 of the same confirm_numbers (you'd have to be unlucky, but it can happen). 2 hits at the same time = 2 selects getting the same max(confirm_number), which results in 2 inserts with the same number. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Chris wrote: Jim Lucas wrote: [EMAIL PROTECTED] wrote: Ok, so just that I am clear, you are SELECTing and pulling all the data that you are submitting in the above INSERT statement from the DB initially, then you are only modifying the confirm_number value and then re- submitting all the values, as they originally were, Well, actually when all is said and done, a new record will be created with new information (Name, phone, email, etc) and the confirm_number is the previous+1 Seems like a perfect candidate for an auto-inc field, though mysql doesn't let you have multiple in the same table (afaik). I would agree, but I'm not the OP. He/She wanted it this way... You do have a race condition, you can end up with 2 of the same confirm_numbers (you'd have to be unlucky, but it can happen). 2 hits at the same time = 2 selects getting the same max(confirm_number), which results in 2 inserts with the same number. Granted that their is a possibility that it could happen. But the chance of it happening with the three statements running back-to-back in the same call is much lower then having three separate calls and doing the math in PHP. -- 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] mySQL query question
Michael S. Dunsavage wrote: On Fri, 2008-11-14 at 12:46 -0800, Jim Lucas wrote: SELECT @confirm_number AS confirm_number; Are we not SELECTING the column value here? should we be selecting confirm_number as confirm_number? The idea is to give you the number that was used in the INSERT statement. It might have changed since the INSERT. Never know. So, giving you the one used in the INSERT is the best way to make sure you get the one you are expecting. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 08:46 +0100, Jochem Maas wrote: 1000 + 1 != 10001 you might consider setting a default of 1000 or 1 or whatever on the given field so it's automatically populated with that number when a contact record is created. Sorry. Hit the 0 one to few times. -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
okay I want to pull an integer from a database called confirm_number, add 1 and repost it back to the database here's the code I'm using. $queryconfirm=SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1; I assume that you are already aware that if you set the variable $queryconfirm to this SQL query that the query is not necessarily executed and returns the result into the variable. $confirmresult=$queryconfirm; Now what you did is that $confirmresult is now: SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1 as well as $queryconfirm. Why is that? now here's the problem I want to add 1 to confirm_number: $confirm_number=$confirmresult; $confirm_number+=1; I will also assume that you don't think that $confirm_number will magically contain the result. Now all this does is set the confirm_number record to 1 in the database. So I assume that $queryconfirm somehow is not being passed to confirm_number? But, while we're here the confirm_number is supposed to be 5 digits long and the +1 should make it 10001, 10002, 10003 etc how would I set the original number to 1000 to begin with? Or should I just set that in the database record its self when I'm ready to use the website? If what I assume is right (I don't want to disappoint you) but you should start reading some PHP basics on how you make database connection in PHP. Use google to find some tutorial for e.g. If I am wrong post us the code to see if someone can help. -- Thodoris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
I would create a separate table for this (confirmation_numbers or something) with an autoincrement primary key. That way you can simply insert a new record for you contact and then ask (using mysql_insert_id()) what the confirmation number is. This approach is much safer as you can be 100% sure the number is unique and it's much less complicated. (of course you still need to check if the query didn't fail) - http://devshed.excudo.net http://devshed.excudo.net -- View this message in context: http://www.nabble.com/mySQL-query-question-tp20495466p20501473.html Sent from the PHP - General mailing list archive at Nabble.com. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, Nov 14, 2008 at 9:58 AM, [EMAIL PROTECTED] wrote: okay I want to pull an integer from a database called confirm_number, add 1 and repost it back to the database No, you don't want to do that. :-) You are introducing a race condition between TWO users who hit the same page at the same time. They each get, say, 42, and they each put back 43, but one of them should have been 44. What you WANT to do is this: update contacts set confirm_number = confirm_number + 1 order by contact desc limit 1 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php yep, our current app, designed by 'brighter minds' than mine, refused to make these auto numbers and now we have problems caused by the race condition. -- Bastien Cat, the other other white meat
Re: [PHP] mySQL query question
update contacts set confirm_number = confirm_number + 1 order by contact desc limit 1 Here is the php query I've been using to send the record in the first place $query=INSERT INTO contacts (first_name, last_name, email, phn_number, address, city, state, zip, dates, comments, confirm_number) VALUES ('$FirstName', '$LastName', '$Email', '$Phone', '$Address', '$City', '$selected_state', '$Zip', '$newdate', '$Comments' , '$confirm_number' ); [EMAIL PROTECTED] ($query); (obviously in the script, it's all on one line) Now, what I need to do, is somehow pull make confirm_number get submitted as a new record, which will happen once they submit the form, but I want it to be submitted +1. (12345 should now be 12346 but a new record entirely) I was trying this code before the submission query: $confirmnumber=SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1; $confirm_number=$confirmnumber+1; Now what this is doing for me so far, is just taking the first numeral of the record, which happens to be 4 (I originally added the confirm_number via the rand function, but have since taken that out) and adding 1 to it and that is it. So the new $confirm_number=5 So it sort of did the job, but not quite.. The question is how can I make a new record (I know the answer to that part) BUT with a confirm_number of 1 greater than the previous record. -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
[EMAIL PROTECTED] wrote: update contacts set confirm_number = confirm_number + 1 order by contact desc limit 1 Here is the php query I've been using to send the record in the first place $query=INSERT INTO contacts (first_name, last_name, email, phn_number, address, city, state, zip, dates, comments, confirm_number) VALUES ('$FirstName', '$LastName', '$Email', '$Phone', '$Address', '$City', '$selected_state', '$Zip', '$newdate', '$Comments' , '$confirm_number' ); [EMAIL PROTECTED] ($query); (obviously in the script, it's all on one line) Now, what I need to do, is somehow pull make confirm_number get submitted as a new record, which will happen once they submit the form, but I want it to be submitted +1. (12345 should now be 12346 but a new record entirely) I was trying this code before the submission query: $confirmnumber=SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1; $confirm_number=$confirmnumber+1; Now what this is doing for me so far, is just taking the first numeral of the record, which happens to be 4 (I originally added the confirm_number via the rand function, but have since taken that out) and adding 1 to it and that is it. So the new $confirm_number=5 So it sort of did the job, but not quite.. The question is how can I make a new record (I know the answer to that part) BUT with a confirm_number of 1 greater than the previous record. Ok, so just that I am clear, you are SELECTing and pulling all the data that you are submitting in the above INSERT statement from the DB initially, then you are only modifying the confirm_number value and then re-submitting all the values, as they originally were, back to the DB and creating a copy of the original data. The only difference being that the $confirm_number has been altered. Correct? -- 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] mySQL query question
On Fri, Nov 14, 2008 at 1:22 PM, [EMAIL PROTECTED] wrote: update contacts set confirm_number = confirm_number + 1 order by contact desc limit 1 Here is the php query I've been using to send the record in the first place $query=INSERT INTO contacts (first_name, last_name, email, phn_number, address, city, state, zip, dates, comments, confirm_number) VALUES ('$FirstName', '$LastName', '$Email', '$Phone', '$Address', '$City', '$selected_state', '$Zip', '$newdate', '$Comments' , '$confirm_number' ); [EMAIL PROTECTED] ($query); (obviously in the script, it's all on one line) Now, what I need to do, is somehow pull make confirm_number get submitted as a new record, which will happen once they submit the form, but I want it to be submitted +1. (12345 should now be 12346 but a new record entirely) I was trying this code before the submission query: $confirmnumber=SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1; $confirm_number=$confirmnumber+1; Now what this is doing for me so far, is just taking the first numeral of the record, which happens to be 4 (I originally added the confirm_number via the rand function, but have since taken that out) and adding 1 to it and that is it. So the new $confirm_number=5 So it sort of did the job, but not quite.. The question is how can I make a new record (I know the answer to that part) BUT with a confirm_number of 1 greater than the previous record. -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php make that field an autonumber and let the database assign it. much much cleaner and simple to get the number with mysql_last_insert() -- Bastien Cat, the other other white meat
Re: [PHP] mySQL query question
Ok, so just that I am clear, you are SELECTing and pulling all the data that you are submitting in the above INSERT statement from the DB initially, then you are only modifying the confirm_number value and then re- submitting all the values, as they originally were, Well, actually when all is said and done, a new record will be created with new information (Name, phone, email, etc) and the confirm_number is the previous+1 This whole thing is a contact form. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
[EMAIL PROTECTED] wrote: Ok, so just that I am clear, you are SELECTing and pulling all the data that you are submitting in the above INSERT statement from the DB initially, then you are only modifying the confirm_number value and then re- submitting all the values, as they originally were, Well, actually when all is said and done, a new record will be created with new information (Name, phone, email, etc) and the confirm_number is the previous+1 This whole thing is a contact form. Well, in that case, you might be able to do something along the lines of this. I tested this on my server: Server version: 5.0.51a-log MySQL client version: 5.0.51a using phpMyAdmin - 2.11.1.2 I have modified an example from this page: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html ?php # # Setup database stuff, process input, get everything ready to do the insert. # # Now prepare your statement $SQL = SET @confirm_number=(SELECT (MAX(confirm_number)+1) FROM `contacts`); INSERT INTO `contacts` ( `first_name`, `last_name`, `email`, `phn_number`, `address`, `city`, `state`, `zip`, `dates`, `comments`, `confirm_number` ) VALUES ( '{$FirstName}', '{$LastName}', '{$Email}', '{$Phone}', '{$Address}', '{$City}', '{$selected_state}', '{$Zip}', '{$newdate}', '{$Comments}', @confirm_number ) SELECT @confirm_number AS confirm_number; ; $confirm_number = NULL; # Run it and get confirm_number to work with now. if ( ($result = @mysql_query($SQL)) !== FALSE ) { list($confirm_number) = mysql_fetch_row($result); } if ( is_null($confirm_number) ) { echo 'Failed to get number'; } ? Obviously, I can't test this without your schema. So, I hope it works. In the end, you should have a result set that gets returned that contains the 'confirm_number' of the newly created entry. This should also, pretty much, eliminate any chance of a race condition. Since everything is happening within mysql, it should be very hard to end up with a condition that you start stomping on records. Let the list know if it works for you. -- 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] mySQL query question
Jim Lucas wrote: [EMAIL PROTECTED] wrote: Ok, so just that I am clear, you are SELECTing and pulling all the data that you are submitting in the above INSERT statement from the DB initially, then you are only modifying the confirm_number value and then re- submitting all the values, as they originally were, Well, actually when all is said and done, a new record will be created with new information (Name, phone, email, etc) and the confirm_number is the previous+1 This whole thing is a contact form. Well, in that case, you might be able to do something along the lines of this. I tested this on my server: Server version: 5.0.51a-log MySQL client version: 5.0.51a using phpMyAdmin - 2.11.1.2 I have modified an example from this page: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html ?php # # Setup database stuff, process input, get everything ready to do the insert. # # Now prepare your statement $SQL = SET @confirm_number=(SELECT (MAX(confirm_number)+1) FROM `contacts`); INSERT INTO `contacts` ( `first_name`, `last_name`, `email`, `phn_number`, `address`, `city`, `state`, `zip`, `dates`, `comments`, `confirm_number` ) VALUES ( '{$FirstName}', '{$LastName}', '{$Email}', '{$Phone}', '{$Address}', '{$City}', '{$selected_state}', '{$Zip}', '{$newdate}', '{$Comments}', @confirm_number ) The above should be this instead @confirm_number ); SELECT @confirm_number AS confirm_number; ; $confirm_number = NULL; # Run it and get confirm_number to work with now. if ( ($result = @mysql_query($SQL)) !== FALSE ) { list($confirm_number) = mysql_fetch_row($result); } if ( is_null($confirm_number) ) { echo 'Failed to get number'; } ? Obviously, I can't test this without your schema. So, I hope it works. In the end, you should have a result set that gets returned that contains the 'confirm_number' of the newly created entry. This should also, pretty much, eliminate any chance of a race condition. Since everything is happening within mysql, it should be very hard to end up with a condition that you start stomping on records. Let the list know if it works for you. -- 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] mySQL query question
On Fri, 2008-11-14 at 13:31 -0800, Jim Lucas wrote: '{$Comments}', @confirm_number ) The above should be this instead @confirm_number ); Even after fixing that, nothing gets inserted into the database. I've been all over the variables and column names and the naming is correct. -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Michael S. Dunsavage wrote: On Fri, 2008-11-14 at 13:31 -0800, Jim Lucas wrote: '{$Comments}', @confirm_number ) The above should be this instead @confirm_number ); Even after fixing that, nothing gets inserted into the database. I've been all over the variables and column names and the naming is correct. Take the @ off the mysql_query() and also check into mysql_error() function. -- 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] mySQL query question
Jim Lucas wrote: Michael S. Dunsavage wrote: On Fri, 2008-11-14 at 13:31 -0800, Jim Lucas wrote: '{$Comments}', @confirm_number ) The above should be this instead @confirm_number ); Even after fixing that, nothing gets inserted into the database. I've been all over the variables and column names and the naming is correct. Take the @ off the mysql_query() and also check into mysql_error() function. also, try it with a striped down version of the insert, just inserting the confirm_number INSERT INTO contacts (confirm_number) VALUES (@confirm_number); and see if that creates a new record. -- 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] mySQL query question
On Fri, 2008-11-14 at 12:46 -0800, Jim Lucas wrote: SELECT @confirm_number AS confirm_number; Are we not SELECTING the column value here? should we be selecting confirm_number as confirm_number? -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
If you're just adding one, there is no reason to retrieve the data, process it, and update it. You can just update the number. http://dev.mysql.com/doc/refman/5.0/en/update.html Also, you should read the MySQL manual on default values: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Michael S. Dunsavage wrote: okay I want to pull an integer from a database called confirm_number, add 1 and repost it back to the database here's the code I'm using. $queryconfirm=SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1; $confirmresult=$queryconfirm; now here's the problem I want to add 1 to confirm_number: $confirm_number=$confirmresult; $confirm_number+=1; Now all this does is set the confirm_number record to 1 in the database. So I assume that $queryconfirm somehow is not being passed to confirm_number? But, while we're here the confirm_number is supposed to be 5 digits long and the +1 should make it 10001, 10002, 10003 etc how would I set the original number to 1000 to begin with? Or should I just set that in the database record its self when I'm ready to use the website? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 00:52 -0600, Micah Gersten wrote: If you're just adding one, there is no reason to retrieve the data, process it, and update it. You can just update the number. http://dev.mysql.com/doc/refman/5.0/en/update.html But, the problem is that the confirm_number is a confirmation number that gets e-mailed out. So I have to pull it from the DB any way -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Michael S. Dunsavage schreef: okay I want to pull an integer from a database called confirm_number, add 1 and repost it back to the database here's the code I'm using. $queryconfirm=SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1; $confirmresult=$queryconfirm; now here's the problem I want to add 1 to confirm_number: $confirm_number=$confirmresult; $confirm_number+=1; Now all this does is set the confirm_number record to 1 in the database. So I assume that $queryconfirm somehow is not being passed to AFAIKT your not querying the DB at all and your merely adding 1 to a string, which results in 1. adding 1 to a php variable will never cause and update in a database ... well actually there might be a way to do that but I can't think of it using some kind of hyper funky object but I'm pretty sure there is no way to overload the + operator. confirm_number? But, while we're here the confirm_number is supposed to be 5 digits long and the +1 should make it 10001, 10002, 10003 etc how would I set the original number to 1000 to begin with? Or should I just set that in the database record its self when I'm ready to use the website? 1000 + 1 != 10001 you might consider setting a default of 1000 or 1 or whatever on the given field so it's automatically populated with that number when a contact record is created. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MYSQL Query question
Reinhart Viane wrote: And a last question: I always seem to get stuck on mysql queries when scripting. mysql.com gives me a headache whens earching something. Does someone know a good mysql manual site or a good mysql book? That does not mean mysql questions should be posted on php mailing lists. STRICTLY PERSONAL AND CONFIDENTIAL This message may contain confidential and proprietary material for the sole use of the intended recipient. Any review or distribution by others is strictly prohibited. If you are not the intended recipient please contact the sender and delete all copies. Do you know that mailing lists are automatically archived at thousands of websites? -- Raditha Dissanayake. -- http://www.radinks.com/print/card-designer/ | Card Designer Applet http://www.radinks.com/upload/ | Drag and Drop Upload -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MYSQL Query question
First, since it was a combined question of php and mysql I thought of sending it here. Secondly, this is my standard footer. My appologizes if my question irritates you -Original Message- From: Raditha Dissanayake [mailto:[EMAIL PROTECTED] Sent: donderdag 9 december 2004 14:14 To: [EMAIL PROTECTED] Subject: Re: [PHP] MYSQL Query question Reinhart Viane wrote: And a last question: I always seem to get stuck on mysql queries when scripting. mysql.com gives me a headache whens earching something. Does someone know a good mysql manual site or a good mysql book? That does not mean mysql questions should be posted on php mailing lists. STRICTLY PERSONAL AND CONFIDENTIAL This message may contain confidential and proprietary material for the sole use of the intended recipient. Any review or distribution by others is strictly prohibited. If you are not the intended recipient please contact the sender and delete all copies. Do you know that mailing lists are automatically archived at thousands of websites? -- Raditha Dissanayake. -- http://www.radinks.com/print/card-designer/ | Card Designer Applet http://www.radinks.com/upload/ | Drag and Drop Upload -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MYSQL Query question
Raditha Dissanayake wrote: Reinhart Viane wrote: And a last question: I always seem to get stuck on mysql queries when scripting. mysql.com gives me a headache whens earching something. Does someone know a good mysql manual site or a good mysql book? That does not mean mysql questions should be posted on php mailing lists. snip There you go again, trying to keep this list on topic. ;) -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MYSQL Query question
John Nichel wrote: Raditha Dissanayake wrote: Reinhart Viane wrote: And a last question: I always seem to get stuck on mysql queries when scripting. mysql.com gives me a headache whens earching something. Does someone know a good mysql manual site or a good mysql book? That does not mean mysql questions should be posted on php mailing lists. snip There you go again, trying to keep this list on topic. ;) Alright I am going to change. as they say if you can't beat them join them. obligatory off topic post: Do you think it's better to use innodb type tables or myisam type tables when using mysql even without foreign keys? -- Raditha Dissanayake. -- http://www.radinks.com/print/card-designer/ | Card Designer Applet http://www.radinks.com/upload/ | Drag and Drop Upload -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql Query Question
$string = ' . implode(',',$group) . '; $query = SELECT * FROM table WHERE groupname IN ($string); ---John Holmes... - Original Message - From: Van Andel, Robbert [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 3:51 PM Subject: [PHP] Mysql Query Question I am trying to query a database looking for results that match an array of values I have set. The variable array is call group and has about 10 elements. Is there a way that I can query the database using just $group instead of having to use the following: SELECT * FROM `table` WHERE groupname = '$group[1]' OR groupname='$group[2]' OR groupname='$group[3]. I think I saw the answer once before but can't find it anywhere. Robbert van Andel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql Query Question
Thanks, I knew it was something simple but I couldn't find it on the 'Net. Robbert van Andel -Original Message- From: CPT John W. Holmes [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 1:14 PM To: Van Andel, Robbert; [EMAIL PROTECTED] Subject: Re: [PHP] Mysql Query Question $string = ' . implode(',',$group) . '; $query = SELECT * FROM table WHERE groupname IN ($string); ---John Holmes... - Original Message - From: Van Andel, Robbert [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 3:51 PM Subject: [PHP] Mysql Query Question I am trying to query a database looking for results that match an array of values I have set. The variable array is call group and has about 10 elements. Is there a way that I can query the database using just $group instead of having to use the following: SELECT * FROM `table` WHERE groupname = '$group[1]' OR groupname='$group[2]' OR groupname='$group[3]. I think I saw the answer once before but can't find it anywhere. Robbert van Andel -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query question
If you are using a table with AUTO_INCREMENT set for one of the fields, the default is for MySQL to assign any new row an AUTO_INCREMENT value that is one higher than the currently highest value in that column. In other words, MySQL by default does exactly what you say you are trying to do. Unless there is some sort of optimization reason for wishing to manipulate the actual location of the data within the table, you shouldn't try to force MySQL to store the row in any particular place -- just let MySQL do the data work, and you just focus on constructing queries that are optimized according to the rules of relational database SQL. It shouldn't matter to you where MySQL actually puts the data, only whether or not your queries will return the results you want in a timely fashion. Erik On Thursday, January 24, 2002, at 06:37 PM, Phil Schwarzmann wrote: Okay, so when I INSERT a row of information into a MySQL database, I don't want it to place it at the first empty row it findsI want it to put it after the very last exsisting row. One of the fields of the database is an AUTO_INCREMENT type, so Im assuming Im gonna use that in the query. How would I write a query to do this?? THANKS!! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL query question
If you're trying to be able to keep reference to the order the records are added to the table, simply add a date/time field and populate it with the date/time now() when you insert. You can then sort on that as needed later. - Original Message - From: Phil Schwarzmann [EMAIL PROTECTED] Subject: [PHP] MySQL query question Okay, so when I INSERT a row of information into a MySQL database, I don't want it to place it at the first empty row it findsI want it to put it after the very last exsisting row. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL Query Question
In article [EMAIL PROTECTED], [EMAIL PROTECTED] ("Jeff Holzfaster") wrote: I have a couple questions... first, is there a notable MySQL General List like this one? Yes. The list on mysql.com is quite active and, like this one, it's common to get responses directly from one of the developers. (Another similarity: the MySQL list is also archived at marc.theaimesgroup.com) The most notable difference is that AFAIK there is no official news mirror of the MySQL list as there is for the PHP.net lists. Darn it. Second, how do you write this query properly, or can it be done? select concat(date_format(date, "%W, %e %M %Y")," ",time) as date from TABLE order by date DESC; The MySQL manual has a chapter on date/time fuctions. Or is it the 'order by' clause that's giving you trouble...? I assume that 'date' is a reserved word, so it may be confusing mysql. In which case, try using a different alias, like 'f_date". -- CC -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] MySQL Query Question
The MySQL manual has a chapter on date/time factions. Or is it the 'order by' clause that's giving you trouble...? I assume that 'date' is a reserved word, so it may be confusing mysql. In which case, try using a different alias, like 'f_date". This works: select date_format(date, "%W, %e %M %Y") as date from table This doesn't: select concat(date_format(date, "%W, %e %M %Y")," ",time) as time_of_day I'm wondering if it is possible to use concat in this way and how if it is possible. Maybe this is a question better suited for the MySQL list. Thanks! Jeff -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] MySQL Query Question
In article [EMAIL PROTECTED], [EMAIL PROTECTED] ("Jeff Holzfaster") wrote: This works: select date_format(date, "%W, %e %M %Y") as date from table This doesn't: select concat(date_format(date, "%W, %e %M %Y")," ",time) as time_of_day I'm wondering if it is possible to use concat in this way and how if it is possible. Maybe this is a question better suited for the MySQL list. Yu'huh. g Questions about making MySQL do stuff -- MySQL list Questions about making PHP do stuff -- PHP lists Questions about making PHP MySQL play nice with each other...it's a judgement call. IMO, the PHP-DB list is the best (certainly the most thematically appropriate) place for PHP/MySQL integration issues, but YMMV. -- CC -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]