Re: [PHP-DB] Getting last insert id?

[Jeff Shapiro]

Well, that all depends on which db server you are using.

On Wed, 8 Oct 2003 18:32:55 -0400, Chris Payne spoke thusly about 
[PHP-DB] Getting last insert id?:
 Hi there everyone,
 
 I use the below to insert some data into my database:
 
 
 mysql_query (INSERT INTO forummessages (Postedby, email, subject, 
 message, ForumID, posted, parentid) 
VALUES ('$Postedby', '$email', '$subject', '$message', 
 '$ForumID','$posted','$parentid')
 );
 
 But once it has done this, I need to get the id it has generated in 
 the DB and then update that record with the id it generate, so how 
 can I get the id from the above insert?
 
 Any help would be greatly appreciated :-)
 
 Regards
 
 Chris

---
Listserv only address.
Jeff Shapiro

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

Re: [PHP-DB] HELP With UPDATE Query in mySQL

[Jeff Shapiro]

This should do it:

UPDATE Bookings
SET
Booking_Start_Date = CONCAT(DATE_FORMAT(Booking_Start_Date, 
'%Y-%m-%d'), '09:00:00'),
Booking_End_Date = CONCAT(DATE_FORMAT(Booking_End_Date, '%Y-%m-%d'), 
'17:30:00');

If you are using version 4.1.1 or newer the DATE_FORMAT function could 
be replaced by:
DATE(Booking_Start_Date)


On Fri, 3 Oct 2003 22:54:49 +0100, Shaun spoke thusly about [PHP-DB] 
HELP With UPDATE Query in mySQL:
 Hi,
 
 I have two columns in my Bookings table of type DATETIME -
 Booking_Start_Date and Boking_End_Date. How can i update every row so that
 all of the times for Booking_Start_Date are 09.00 and all of the times for
 Booking_End_Date are 17.30, without affecting any of the dates?
 
 Thanks for your help

---
Listserv only address.
Jeff Shapiro

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

Re: [PHP-DB] Help With Another UPDATE Query Please!

[Jeff Shapiro]

It all depends on what database server (and version) you are using. I'm 
not really sure what you are asking, and could use a little more detail 
about the tables and what are needing to do. 

On Sat, 4 Oct 2003 13:45:56 +0100, Shaun spoke thusly about [PHP-DB] 
Help With Another UPDATE Query Please!:
 Hi,
 
 I am making some alterations to my Database. I have a table called projects
 and a table called Work_Types. Projects currently contains the name of the
 work type (Work_Type) but now I want to change this so it contains the
 Work_Type_ID, is it possible to update Projects with one query?
 
 Thanks for your help

---
Listserv only address.
Jeff Shapiro

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

Re: [PHP-DB] how to select rows with repeated coloumns in one query????

[Jeff Shapiro]

Actually, it's MySQL 4.1 and above that supports subselects.

On Tue, 30 Sep 2003 12:24:51 +0530, Nitin spoke thusly about Re: 
[PHP-DB] how to select rows with repeated coloumns in one query:
 with Mysql 4.0 and above, u can use subqueries, so that u can put first
 query in the where clause of second query itself.
 
 Nitin
 
 - Original Message - 
 From: Saurabh Dhawan [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Tuesday, September 30, 2003 11:26 AM
 Subject: [PHP-DB] how to select rows with repeated coloumns in one query
 
 
  I have a table called studs with coloumns as :
  Sno. name grade
  1 aa 1
  2 bb 1
  3 cc 2
  4 dd 3
  5 ee 4
  6 ff 4
  7 gg 4
  8 hh 5
 
  Now i want to see only those rows which have value of grade repeated
  atleast in one other row i.e.
  The result should contain row no's 1,2(with grade 1) and 5,6,7(with grade
 4)
 
  in two queries this cud be done as :
  select sno, count(grade) as cnt from studs group by grade having cnt1
  and then
  select * from studs where sno in ('result of previous query')
 
  -- 
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php

---
Listserv only address.
Jeff Shapiro

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

Re: [PHP-DB] sql optimizing assistance

[Jeff Shapiro]

What is the exact order of columns in your PRIMARY KEY in the Response 
table? If Question_Key is not the first column listed in the key, then 
MySQL won't use it.

Try the following:
SELECT Question.Text_Long, AVG( Response ) 
FROM `Response` USE INDEX (Question_key)
JOIN Question ON Response.Question_Key = Question.Question_Key 
WHERE Question.Question_Key LIKE '2003%' 
GROUP BY Response.Question_Key 
ORDER BY Question.Question_Key ASC

This should force MySQL to use your index.

According to the MySQL manual the LIKE function does use indexes when 
you use it the way that you are using it. For more info: 
http://www.mysql.com/doc/en/MySQL_indexes.html


On Fri, 19 Sep 2003 16:01:34 -0400, [EMAIL PROTECTED] spoke 
thusly about Re: [PHP-DB] sql optimizing assistance:
 
 i new i forgot to include something.
 
 here this is the ouput.  it looks like its using none of my indexes in the
 Response, which is where i would think it would need it the most.
 
 
+++---++-++++
  table  | type   | possible_keys | key| key_len | ref| rows   |
 Extra  |
 
+++---++-++++
  Response| ALL| [NULL]| [NULL] | [NULL]  | [NULL] | 126732 |
 Using temporary; Using filesort|
  Question| eq_ref | PRIMARY   | PRIMARY| 4   |
 Response.Question_Key| 1  | where used|
 
+++---++-++++
 
 even on the second query where there is no join, its not using the keys.
 
+++---++-++++
  table  | type   | possible_keys | key| key_len | ref| rows   |
 Extra  |
 
+++---++-++++
  Response| ALL| [NULL]| [NULL] | [NULL]  | [NULL] | 126732 |
 Using temporary|
 
+++---++-++++
 
 Thanks,
 Jeff
 
 
  
   
   CPT John 
 
W. 

   Holmes  To:   
 [EMAIL PROTECTED], 
 [EMAIL PROTECTED]
   [EMAIL PROTECTED]
 
cc:
 
   rter.netSubject:  Re: [PHP-DB] 
 sql optimizing assistance
  
   
   09/19/2003 
 
03:59  
   
   
 
PM 
  
   Please respond 
 
to 
   
   CPT John 
 
W. 

   
 
Holmes
  
  
   
  
   
 
 
 
 
 From: [EMAIL PROTECTED]
 
 
  I have two tables and am running a simple join between them to get
  questions and their repsective response averages from a survey.  The
  question table has 49 rows and the Response table has 126,732.  I'd like
 to
  cut down on the time its taking to run this specific query...as i'll be
  running many like it to generate reports. The query below is the
 selecting
  the most data, normally this will be limited to specific groups by
 joining
  more tables.
 
  I am executing the following query
  SELECT Question.Text_Long, AVG( Response ) FROM `Response` INNER JOIN
  Question ON Question.Question_Key = Response.Question_Key WHERE
  Question.Question_Key LIKE '2003%' GROUP BY Response.Question_Key ORDER
 BY
  Question.Question_Key ASC
 
 What does EXPLAIN tell you for this query? Is it using your indexes?
 
 ---John Holmes...

---
Listserv only address.
Jeff Shapiro

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

Re: [PHP-DB] Consider upgrading MySQL client .... how do I do this?

[Jeff Shapiro]

Sorry about coming into this thread late.

You should read this page:
http://www.mysql.com/doc/en/Password_hashing.html

It details how to upgrade your user tables to MySQL 4.1.x and still 
allow old clients to connect to the server.

On Wed, 03 Sep 2003 19:50:37 +1000, Vinoaj Vijeyakumaar spoke thusly 
about [PHP-DB] Consider upgrading MySQL client  how do I do this?:
 Hi,
 
 Just this morning I upgraded to MySQL 4.1.0 alpha and PHP 4.3.3.  I'm 
 also running Apache 2 on a WinXP machine.  The main reason for the 
 upgrade was so that I could make use of 4.1.0's sub-select feature 
 rather than attempt learning JOINs.  It is now 7 hours later, and I 
 keep getting the following error when trying to connect to my 
 database:
 
 Warning: mysql_connect(): Client does not support authentication 
 protocol requested by server. Consider upgrading MySQL client
 
 Running phpinfo() shows Client API version 3.23.49 under mySQL.
 
 Searching around the net all day, the closest explanation I could 
 find was that PHP does not have a client API to interface with MySQL 
 4.1.x.  The solution suggested was to use an ODBC driver to interface 
 between PHP and MySQL but indicated that this may be slower.
 
 My question, therefore, is - is there a way to upgrade the MySQL 
 client in PHP 4.3.3?  If not, what are the alternatives (apart from 
 reverting back to MySQL 4.0.x of course).
 
 Thanks in advance.
 
 Vinny.
 
 

---
Listserv only address.
Jeff Shapiro

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

Re: [PHP-DB] MySQL, PHP, and XML

[Jeff Shapiro]

A quick google search produced this:

Creating XML from MySQL (it also talks about importing XML)
http://www.zdnet.com.au/builder/architect/database/story/0,234918,20266023,00.htm



On Wed, 11 Jun 2003 21:53:56 -0500, [EMAIL PROTECTED] wrote:
 Is there an automatic way to insert an XML file into a MySQL DB through say,
 Load Data InFILE?  
 
 Or does one have to Pick apart the XML with PHP and insert data into the
 fields one by one, record by record?
 
 /T
 
 
 
 
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php

---
Listserv only address.
Jeff Shapiro

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

Re: [PHP-DB] problem with insert query

[Jeff Shapiro]

It seems to me that this code will not provide useful information:

if ($result)
echo mysql_affected_rows(). affected.;

If the insert was successful, it will always say 1 affected. Since 
you are only inserting one row at a time. 

On Sat, 07 Jun 2003 08:49:15 -0400, Becoming Digital wrote:
 Try this.  I made some minor syntax changes and added some error reporting.
 While it may not fix things, it might help you figure out what's wrong.
 
 ?
 $mails=file(mails.txt);
 $number_of_mails = count($mails);
 
 for ($i=0; $i$number_of_mails; $i++)
 {
 $link = @ mysql_connect(localhost)
 or die( mysql_error() );
 $db = mysql_select_db(me2resh00, $link);
 $query = insert into recipients (recipient_name, 
 recipient_email) values
 (\friend\, \.$mails[$i].\);
 $result = mysql_query($query);
 if ($result)
 echo mysql_affected_rows(). affected.;
 }
 ?
 
 Edward Dudlik
 Becoming Digital
 www.becomingdigital.com
 
 
 - Original Message -
 From: Ahmed Abdelaliem [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Saturday, 07 June, 2003 08:15
 Subject: [PHP-DB] problem with insert query
 
 
 hi
 i am wrote a script to read e-malis from a file and insert it into the
 tables of database
 i get error when i write it
 here is the code
 can anyone tell me what is wrong?
 
 ?
 $mails=file(mails.txt);
 $number_of_mails = count($mails);
 
 for ($i=0; $i$number_of_mails; $i++){
 
  @ $db = mysql_connect(localhost);
 mysql_select_db(me2resh00);
 $query = insert into recipients (recipient_name, recipient_email) values
 ('friend', '.$mails[$i].');
 $result = mysql_query($query);
 if ($result)
 echo mysql_affected_rows(). affected.;
 
}
  ?
 
 
 here is the error that i get
 
 Parse error: parse error in enter.php on line 9
 
 
 and line 9 is
 
 $query = insert into recipients (recipient_name, recipient_email) values
 ('friend', '.$mails[$i].');
---
Listserv only address.
Jeff Shapiro

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

Re: [PHP-DB] How to find the last ID?

[Jeff Shapiro]

$last_id = mysql_insert_id();

Check out this page for details.
http://www.php.net/manual/en/function.mysql-insert-id.php

On Fri, 6 Jun 2003 17:16:01 -0400, Chris Payne wrote:
 Hi there everyone,
 
 I'm creating a new entry using the following:
 
 mysql_query (INSERT INTO agents (agent_name) 
VALUES ('$agentname')
 
 but I need to find out the ID value it created, how can I do this easily?
 
 Thanks
 
 Chris

---
Listserv only address.
Jeff Shapiro

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