[PHP] MySQL+PHP
Dear all, I'm a newbie, need your help. Now I am building a databasea using MySQL and PHP, I want to put two tables in one searching, the two are in one database. $query = SELECT SQL_CALC_FOUND_ROWS * FROM table1 WHERE .implode( AND , $data); $number = SELECT FOUND_ROWS(); How can I change this if I want to put table2 in it. Thank you. Best wishes, diana 2010-7-21
[PHP] mysql + PHP
Hi all, I have SQL query , for example , Select country , name from tbl_chassis order by country. The problem of the sql statement is that , if there are empty value in country field , it be sorted first . How to do sorting the empty value last ? I can cp() function to do this ? or any mysql function to use? Thanks - weetat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql + PHP
perhaps this will work: Select country , name from tbl_chassis order by ifnull(country,'') didn't try it. - Original Message - From: weetat [EMAIL PROTECTED] To: php-general@lists.php.net Sent: Thursday, June 15, 2006 8:14 AM Subject: [PHP] mysql + PHP Hi all, I have SQL query , for example , Select country , name from tbl_chassis order by country. The problem of the sql statement is that , if there are empty value in country field , it be sorted first . How to do sorting the empty value last ? I can cp() function to do this ? or any mysql function to use? Thanks - weetat -- 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 + PHP
hi , not working . The empty value still sorted first. Thanks. Satyam wrote: perhaps this will work: Select country , name from tbl_chassis order by ifnull(country,'') didn't try it. - Original Message - From: weetat [EMAIL PROTECTED] To: php-general@lists.php.net Sent: Thursday, June 15, 2006 8:14 AM Subject: [PHP] mysql + PHP Hi all, I have SQL query , for example , Select country , name from tbl_chassis order by country. The problem of the sql statement is that , if there are empty value in country field , it be sorted first . How to do sorting the empty value last ? I can cp() function to do this ? or any mysql function to use? Thanks - weetat -- 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 + PHP
Actually, does the country field contains null or an empty string? If it is an empty string this will nor work. Try Control Flow Functions: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html Satyam - Original Message - From: weetat [EMAIL PROTECTED] To: php-general@lists.php.net; Satyam [EMAIL PROTECTED] Cc: php-general@lists.php.net Sent: Thursday, June 15, 2006 8:51 AM Subject: Re: [PHP] mysql + PHP hi , not working . The empty value still sorted first. Thanks. Satyam wrote: perhaps this will work: Select country , name from tbl_chassis order by ifnull(country,'') didn't try it. - Original Message - From: weetat [EMAIL PROTECTED] To: php-general@lists.php.net Sent: Thursday, June 15, 2006 8:14 AM Subject: [PHP] mysql + PHP Hi all, I have SQL query , for example , Select country , name from tbl_chassis order by country. The problem of the sql statement is that , if there are empty value in country field , it be sorted first . How to do sorting the empty value last ? I can cp() function to do this ? or any mysql function to use? Thanks - weetat -- 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 + PHP
2006/6/15, weetat [EMAIL PROTECTED]: Hi all, I have SQL query , for example , Select country , name from tbl_chassis order by country. The problem of the sql statement is that , if there are empty value in country field , it be sorted first . How to do sorting the empty value last ? I can cp() function to do this ? or any mysql function to use? Thanks - weetat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Maybe this will do the trick: select country, name from tbl_chassis order by country = '', country
Re: [PHP] mysql + PHP
On Thu, June 15, 2006 1:14 am, weetat wrote: I have SQL query , for example , Select country , name from tbl_chassis order by country. The problem of the sql statement is that , if there are empty value in country field , it be sorted first . How to do sorting the empty value last ? I can cp() function to do this ? or any mysql function to use? This is really an SQL question, and the empty values should properly be called NULL to avoid confusion with '', the empty string... Unless you have '' in your data for country, which is really just Bad Data, imho... The solution for NULL is fairly easy. There is an SQL function called 'coalesce' which will convert any NULL value to, err, whatever you want. So: select coalesce(country, 'ZZ'), name from ... would probably be the simplest answer. If, however, your country values are '', and if you don't want to fix the application to avoid such a bogus value, you could do: ORDER BY country = '', country, ... country = '' should turn into true/false, which should turn into 0/1 which should order by the ones that have something first, and the ones that are '' last. You may need to do some kind of 'typecast' on the country = '' depending on your SQL engine. You may even be forced to do: select country, nane, typecast_function(country = '', bool) as empty from ... order by empty, country if your SQL engine is particularly baroque (MySQL 3.23, I do believe...) :-) -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql + PHP
Thank all for your inputs. Yes . the data should be null , really bad data , will try to change database structure. Richard Lynch wrote: On Thu, June 15, 2006 1:14 am, weetat wrote: I have SQL query , for example , Select country , name from tbl_chassis order by country. The problem of the sql statement is that , if there are empty value in country field , it be sorted first . How to do sorting the empty value last ? I can cp() function to do this ? or any mysql function to use? This is really an SQL question, and the empty values should properly be called NULL to avoid confusion with '', the empty string... Unless you have '' in your data for country, which is really just Bad Data, imho... The solution for NULL is fairly easy. There is an SQL function called 'coalesce' which will convert any NULL value to, err, whatever you want. So: select coalesce(country, 'ZZ'), name from ... would probably be the simplest answer. If, however, your country values are '', and if you don't want to fix the application to avoid such a bogus value, you could do: ORDER BY country = '', country, ... country = '' should turn into true/false, which should turn into 0/1 which should order by the ones that have something first, and the ones that are '' last. You may need to do some kind of 'typecast' on the country = '' depending on your SQL engine. You may even be forced to do: select country, nane, typecast_function(country = '', bool) as empty from ... order by empty, country if your SQL engine is particularly baroque (MySQL 3.23, I do believe...) :-) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql + PHP
At 2:14 PM +0800 6/15/06, weetat wrote: Hi all, I have SQL query , for example , Select country , name from tbl_chassis order by country. The problem of the sql statement is that , if there are empty value in country field , it be sorted first . How to do sorting the empty value last ? I can cp() function to do this ? or any mysql function to use? Thanks - weetat From mysql/null: http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html If you want to have NULL values presented last when doing an ORDER BY, try this: SELECT * FROM my_table ORDER BY ISNULL(field), field [ ASC | DESC ] You can also try other combinations, such as (I haven't tried it): SELECT country , name FROM tbl_chassis ORDER BY country AND IS NOT NULL tedd -- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Mysql php Load Data Infiles
While I think this is primarily a mysql syntax problem I have asked on the mysql forum but not got a reply. I am trying using php to set up a system for loading a new data base using the Load Data Infile statement. I could not get it to work properly with php so I thought I would try some command line test. Does anyone know the loaction of a suitable php script that would handle a list of files and tables for loading into a database (preferably doing an initial dummy run using temporary tables with an error report). If not when I get this wretched syntax issue solved I guess I might have one available fairly soon. Here are my notes: This exercise was a test in preparation for a plan to use Load Data Infile for initallising a new database comprising over 80 tables. The database currently has no data. The first field is auto-increment. What am I doing wrong here? If I cannot do it using Load Data Infile what alternatives would you suggest? ___ Here is mysql command line dialogue on a win XP development machine: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 85 to server version: 5.0.13-rc-nt-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use NewBuddies; Database changed mysql load data local infile 'E:/Development/LBuddies/Documemntation/lbcm_diet_ types.txt' REPLACE into table lbcm_diet_types fields terminated by ',' ENCLOSED by ''; ERROR 1265 (01000): Data truncated for column 'Help' at row 1 mysql describe lbcm_diet_types; +-+-+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+-+--+-+-++ | Diet_Type_ID | int(11) | NO | PRI | NULL | auto_increment | | Diet_Type_Title | varchar(30) | NO | UNI | Enter Title | | | Diet_Type_Descr | text | NO | | | | | Help | int(11) | NO | | 1 | | +-+-+--+-+-++ 4 rows in set (0.01 sec) mysql select * from lbcm_diet_types; Empty set (0.00 sec) mysql ___ Here is the content of the first file created as a text file using editor macromedia: ___ 1,Unlimited,Anything goes,1 2,Lacto-Vegetarian,Vegetarian consuming milk products, no meat, no fish,1 3,Fish_Lacto-Vegetarian,Lacto Vegetarian plus fish,1 I then realized my obvious mistake two 's in the third field on line two. I removed REPLACE from the command and ran it again: mysql load data local infile 'E:/Development/LBuddies/Documemntation/lbcm_diet_ types.txt' into table lbcm_diet_types fields terminated by ',' ENCLOSED by ''; Query OK, 2 rows affected, 3 warnings (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 3 mysql select * from lbcm_diet_types; +--+---++--+ | Diet_Type_ID | Diet_Type_Title | Diet_Type_Descr | Help | +--+---++--+ | 1 | Unlimited | Anything goes | 1 | | 3 | Fish_Lacto-Vegetarian | Lacto Vegetarian plus fish | 0 | +--+---++--+ 2 rows in set (0.00 sec) /* OK we have progress BUT */ /*What happened to line 2? */ /* Why 0 for the help ID on line 3? */ /* So I amended the file to read: */ 4,Diabetic,See Diet Sheet,1 5,Glutenfree,No wheat products or other glutenous grains,1 6,Nut allergic,Avoid all nut products,1 /* and with the command:*/ mysql load data local infile 'E:/Development/LBuddies/Documemntation/lbcm_diet_ types.txt' into table lbcm_diet_types fields terminated by ',' ENCLOSED by ''; Query OK, 2 rows affected, 3 warnings (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 3 /* I got:*/ mysql select * from lbcm_diet_types; +--+---++--+ | Diet_Type_ID | Diet_Type_Title | Diet_Type_Descr | Help | +--+---++--+ | 1 | Unlimited | Anything goes | 1 | | 3 | Fish_Lacto-Vegetarian | Lacto Vegetarian plus fish | 0 | | 4 | Diabetic | See Diet Sheet | 1 | | 6 | Nut allergic | Avoid all nut products | 0 | +--+---++--+ 4 rows in set (0.00 sec) mysql /* showing that the second line from this file is again being dropped and the last line gets 0 for help! - I must be missing something obvious but I am damned if I can see it groans /* So I realize it might be due to the windows CR/LF oddity therefore I try: */ mysql load data local infile 'E:/Development/LBuddies/Documemntation/lbcm_diet_
[PHP] mysql/php date functions..
hi... can anybody point me to a good/basic tutorial (tested) for php/mysql date functions... basically, i want to store a date/time in a column, and be able to read it, manipulate it, and update the table. i've seen various articles/sample code, but i'm looking for something that i cna pretty much rewrite and use without having to do a lot of initial testing.. if i create the functions myself, i'm going to need to do initial testing to make sure i haven't screwed something up, and i'm on a time crunch!! thanks -bruce [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
-Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:12 AM To: php-general@lists.php.net Subject: [PHP] mysql/php date functions.. hi... can anybody point me to a good/basic tutorial (tested) for php/mysql date functions... basically, i want to store a date/time in a column, and be able to read it, manipulate it, and update the table. i've seen various articles/sample code, but i'm looking for something that i cna pretty much rewrite and use without having to do a lot of initial testing.. if i create the functions myself, i'm going to need to do initial testing to make sure i haven't screwed something up, and i'm on a time crunch!! Hi Bruce! MySQL and PHP both have extensive built-in date functions that are clearly documented and extraordinarily easy to use. For the vast majority of situations, there is no need to manually write any custom date-handling code. The decision to use MySQL or PHP to manipulate a date for a given instance depends largely on the particulars of that situation. For instance, if you want to increment a MySQL date column by one day, it would likely be better to use the MySQL date functions to do it, because to use PHP, you have to read the date, manipulate it, then write it back, whereas if you use a SQL statement, you can do it with one DB call. See: http://www.php.net/datetime http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Cheers! JM -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
Jim Moseby wrote: Hi Bruce! MySQL and PHP both have extensive built-in date functions that are clearly documented and extraordinarily easy to use. For the vast majority of situations, there is no need to manually write any custom date-handling code. The decision to use MySQL or PHP to manipulate a date for a given instance depends largely on the particulars of that situation. For instance, if you want to increment a MySQL date column by one day, it would likely be better to use the MySQL date functions to do it, because to use PHP, you have to read the date, manipulate it, then write it back, whereas if you use a SQL statement, you can do it with one DB call. See: http://www.php.net/datetime http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Cheers! Just my 0.02 euros. When I need to use dates in PHP *and* MySQL I usually use the UNIX timestamp (seconds since the epoch, that is, the PHP http://php.net/time output). This way I can (quite) easily calculate date differences etc. (usually all the calculations are done in PHP, also when building SQL queries). As I said, just my 0.02 euros... Cheers Silvio -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
how can i create a mysql sql statement to insert a php 'time()' into mysql? i've got the mysql var 't1, timestamp' but i can't figure out how to do an insert $q = time(); $sql = sprintf(insert into foo (id, ctime) values(%d, %???), $id, $q); can't figure out how to get this to work... if i create a sql statement and run it directly within mysql, --insert into foo (id, ctime) values(2, NOW()); this works.. but i can't figure out how to create the sql using the php time() function and getting the insert from the php app... so what's the basic part i'm missing??!! -bruce -Original Message- From: Silvio Porcellana [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 8:51 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. Jim Moseby wrote: Hi Bruce! MySQL and PHP both have extensive built-in date functions that are clearly documented and extraordinarily easy to use. For the vast majority of situations, there is no need to manually write any custom date-handling code. The decision to use MySQL or PHP to manipulate a date for a given instance depends largely on the particulars of that situation. For instance, if you want to increment a MySQL date column by one day, it would likely be better to use the MySQL date functions to do it, because to use PHP, you have to read the date, manipulate it, then write it back, whereas if you use a SQL statement, you can do it with one DB call. See: http://www.php.net/datetime http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Cheers! Just my 0.02 euros. When I need to use dates in PHP *and* MySQL I usually use the UNIX timestamp (seconds since the epoch, that is, the PHP http://php.net/time output). This way I can (quite) easily calculate date differences etc. (usually all the calculations are done in PHP, also when building SQL queries). As I said, just my 0.02 euros... Cheers Silvio -- 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/php date functions..
bruce wrote: how can i create a mysql sql statement to insert a php 'time()' into mysql? i've got the mysql var 't1, timestamp' but i can't figure out how to do an insert $q = time(); $sql = sprintf(insert into foo (id, ctime) values(%d, %???), $id, $q); can't figure out how to get this to work... if i create a sql statement and run it directly within mysql, --insert into foo (id, ctime) values(2, NOW()); this works.. but i can't figure out how to create the sql using the php time() function and getting the insert from the php app... so what's the basic part i'm missing??!! -bruce $sql = INSERT INTO db.table ( id, ctime ) VALUES ( . $id . , . time() . ); -- 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/php date functions..
thanks... but that's not it john... i'm not worried about creating the sql_statement in the php... i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... if i do (from mysql) -- insert into foo (id, time) values (2, 33), it doesn't work... if i -- insert into foo (id, time) values (2, NOW()), it works!!... my question is why??? -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:00 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: how can i create a mysql sql statement to insert a php 'time()' into mysql? i've got the mysql var 't1, timestamp' but i can't figure out how to do an insert $q = time(); $sql = sprintf(insert into foo (id, ctime) values(%d, %???), $id, $q); can't figure out how to get this to work... if i create a sql statement and run it directly within mysql, --insert into foo (id, ctime) values(2, NOW()); this works.. but i can't figure out how to create the sql using the php time() function and getting the insert from the php app... so what's the basic part i'm missing??!! -bruce $sql = INSERT INTO db.table ( id, ctime ) VALUES ( . $id . , . time() . ); -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
along the same way... why can't i see the timestamp designation (ie the double/int) instead of the date-time format when i look at the timestmp var when i do a 'select * from t1' within mysql?? arrgghh! -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:00 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: how can i create a mysql sql statement to insert a php 'time()' into mysql? i've got the mysql var 't1, timestamp' but i can't figure out how to do an insert $q = time(); $sql = sprintf(insert into foo (id, ctime) values(%d, %???), $id, $q); can't figure out how to get this to work... if i create a sql statement and run it directly within mysql, --insert into foo (id, ctime) values(2, NOW()); this works.. but i can't figure out how to create the sql using the php time() function and getting the insert from the php app... so what's the basic part i'm missing??!! -bruce $sql = INSERT INTO db.table ( id, ctime ) VALUES ( . $id . , . time() . ); -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
bruce mailto:[EMAIL PROTECTED] on Monday, September 26, 2005 11:13 AM said: i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... [snip] my question is why??? MySQL timestamps are different from UNIX timestamps. Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
bruce wrote: thanks... but that's not it john... i'm not worried about creating the sql_statement in the php... i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... if i do (from mysql) -- insert into foo (id, time) values (2, 33), it doesn't work... if i -- insert into foo (id, time) values (2, NOW()), it works!!... my question is why??? Without knowing the structure of your table, my guess is that the column time is expecting a valid timestamp, and 33 is not a valid MySQL timestamp. -- 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/php date functions..
but that still doesn't explain why i can't slam some value directly into the timestamp var within the mysql tbl... -Original Message- From: Chris W. Parker [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:15 AM To: php-general@lists.php.net Subject: RE: [PHP] mysql/php date functions.. bruce mailto:[EMAIL PROTECTED] on Monday, September 26, 2005 11:13 AM said: i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... [snip] my question is why??? MySQL timestamps are different from UNIX timestamps. Chris. -- 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/php date functions..
john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:19 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: thanks... but that's not it john... i'm not worried about creating the sql_statement in the php... i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... if i do (from mysql) -- insert into foo (id, time) values (2, 33), it doesn't work... if i -- insert into foo (id, time) values (2, NOW()), it works!!... my question is why??? Without knowing the structure of your table, my guess is that the column time is expecting a valid timestamp, and 33 is not a valid MySQL timestamp. -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
bruce wrote: john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce Personal preference I guess. Me, I use UNIX timestamps. -- 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/php date functions..
-Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 2:46 PM To: 'John Nichel'; php-general@lists.php.net Subject: RE: [PHP] mysql/php date functions.. john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce That depends largely on the situation. For instance, if you want to increment a date in a MySQL table, you would likely do it with a MySQL statement, because to do it in PHP, you need to do a read, then add the day, then a write, (two DB calls). With MySQL, you would just do something like: update `mytable` set 'columnname` date_add(columnname, interval 1 day); One call to the DB instead of two, much more efficient. There are many instances where you would want to use PHP to manipulate dates too, depending on their circumstances. JM -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
so you play with the time/date vars on the php side, and then simply store them in the mysql tbl as a int(10)... or do you actually store the vars in the mysql timestamp... and then use the mysql date/time functions... ie. how would you do the following... get a date (date1) get a date (date2) store the date1/time in mysql add the date1 + date2 and store the result in mysql read the result from mysql, with the result being in the year/month/date format -- or, would you just get the unix_timestamp representation of the dates, and store the 10 int formats in the mysql tbl. you could then extract/select the date information from the tbls, and do all the date calculations in php... the downside to this is that you'd have to convert all the date information from mysql to a human readable format... thoughts/comments/etc... -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:48 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce Personal preference I guess. Me, I use UNIX timestamps. -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql/php date functions..
or.. better yet. if i do a php - time(), i get a unix_timestamp var. how do you guys store this in mysql. you can't simply do an insert into a mysql/timestamp var. so how do you convert it? also, once you have the mysql tbl, how do you go from the mysql timestamp var - the php var? thanks bruce -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 12:38 PM To: 'John Nichel'; php-general@lists.php.net Subject: RE: [PHP] mysql/php date functions.. so you play with the time/date vars on the php side, and then simply store them in the mysql tbl as a int(10)... or do you actually store the vars in the mysql timestamp... and then use the mysql date/time functions... ie. how would you do the following... get a date (date1) get a date (date2) store the date1/time in mysql add the date1 + date2 and store the result in mysql read the result from mysql, with the result being in the year/month/date format -- or, would you just get the unix_timestamp representation of the dates, and store the 10 int formats in the mysql tbl. you could then extract/select the date information from the tbls, and do all the date calculations in php... the downside to this is that you'd have to convert all the date information from mysql to a human readable format... thoughts/comments/etc... -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:48 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce Personal preference I guess. Me, I use UNIX timestamps. -- 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 -- 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/php date functions..
bruce wrote: or.. better yet. if i do a php - time(), i get a unix_timestamp var. how do you guys store this in mysql. you can't simply do an insert into a mysql/timestamp var. so how do you convert it? I don't convert it. I store the UNIX timestamp in an INT(11) column. also, once you have the mysql tbl, how do you go from the mysql timestamp var - the php var? Since I keep it in the UNIX timestamp format, there's no converting between the two. -- 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/php date functions..
John Nichel mailto:[EMAIL PROTECTED] on Monday, September 26, 2005 12:43 PM said: I don't convert it. I store the UNIX timestamp in an INT(11) column. This is going to be a basic question I'm sure but why INT and not VARCHAR? Is it simply because a timestamp is a number? Chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php date functions..
Read up on mysql date types and functions. You can convert a unix timestamp to a mysql date with the FROM_UNIXTIME() function. If you want to store as unix timestamp, store it in an int field. To use mysql date functions on it, use the FROM_UNIXTIME() function. If you want to store dates in mysql timestamp fields, use in a select statement a) UNIX_TIMESTAMP() or b) DATE_FORMAT() to a) convert to unix timestamp and process with PHP or b) format date for output. http://dev.mysql.com/doc/mysql/en/date-and-time-types.html http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html kgt bruce wrote: or.. better yet. if i do a php - time(), i get a unix_timestamp var. how do you guys store this in mysql. you can't simply do an insert into a mysql/timestamp var. so how do you convert it? also, once you have the mysql tbl, how do you go from the mysql timestamp var - the php var? thanks bruce -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 12:38 PM To: 'John Nichel'; php-general@lists.php.net Subject: RE: [PHP] mysql/php date functions.. so you play with the time/date vars on the php side, and then simply store them in the mysql tbl as a int(10)... or do you actually store the vars in the mysql timestamp... and then use the mysql date/time functions... ie. how would you do the following... get a date (date1) get a date (date2) store the date1/time in mysql add the date1 + date2 and store the result in mysql read the result from mysql, with the result being in the year/month/date format -- or, would you just get the unix_timestamp representation of the dates, and store the 10 int formats in the mysql tbl. you could then extract/select the date information from the tbls, and do all the date calculations in php... the downside to this is that you'd have to convert all the date information from mysql to a human readable format... thoughts/comments/etc... -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:48 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce Personal preference I guess. Me, I use UNIX timestamps. -- 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 -- 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/php date functions..
Chris W. Parker wrote: John Nichel mailto:[EMAIL PROTECTED] on Monday, September 26, 2005 12:43 PM said: I don't convert it. I store the UNIX timestamp in an INT(11) column. This is going to be a basic question I'm sure but why INT and not VARCHAR? Is it simply because a timestamp is a number? Yep. Course, I may be doing it wrong. Wouldn't be the first time. ;) -- 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/php date functions..
Hi The PHP time() function returns the number of seconds since the Unix epoch (Jan 1 1970 midnight) whereas MySQL uses a readable datestamp See here: http://dev.mysql.com/doc/mysql/en/datetime.html for the MySQL date, time and date/time types and here http://uk.php.net/manual/en/function.time.php for time()... instead of using time(), use date() and format it for MySQL http://uk.php.net/manual/en/function.date.php That will allow you to use and in the SQL statements HTH Chrome http://www.chrome.me.uk -Original Message- From: bruce [mailto:[EMAIL PROTECTED] Sent: 26 September 2005 19:46 To: 'John Nichel'; php-general@lists.php.net Subject: RE: [PHP] mysql/php date functions.. john... that appears to be it!! although i would have assumes it would have done a most significant bit fill with 0's... so my question also comes down to .. do i use the php date functions for date/time manipulation.. or do i use the mysql functions any thoughts/suggestions... -bruce -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Monday, September 26, 2005 11:19 AM To: php-general@lists.php.net Subject: Re: [PHP] mysql/php date functions.. bruce wrote: thanks... but that's not it john... i'm not worried about creating the sql_statement in the php... i'm concerned that i can't seem to craft/create a basic sql cmd within mysql to get a value (other than NOW()) to work... if i do (from mysql) -- insert into foo (id, time) values (2, 33), it doesn't work... if i -- insert into foo (id, time) values (2, NOW()), it works!!... my question is why??? Without knowing the structure of your table, my guess is that the column time is expecting a valid timestamp, and 33 is not a valid MySQL timestamp. -- 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 -- 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
[PHP] mysql/php date functions..
Hello Bruce, While the date functions are well documented - http://php.net/date - here is a little example to manipulate the date with php, rather than mysql: // set the new expiry date // DATE FUNCTIONS FOR THE EXPIRY MODULE // first perform date arithmetic $listingExpiry = mktime (0,0,0,date(m)+$monthsGoodFor,date(d)+1,date(Y)); // secondly, format the results for use in the database $expires = date (Y-m-d, $listingExpiry); // the actual update of the database with expires='$expires' ... should be below here The date format to be used in $expires depends on your database column structure. Rob. http://www.globalissa.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL + PHP question
Hi guys. I'm having some trouble here regarding a project. I have a table with projects, wich can be recursive (ie. sub-projects) and it is related to itself. By making the following query -8-- SELECT * FROM projects LEFT JOIN projects proj_parent ON projects.project_parent = proj_parent.parent_id WHERE project_id = 1234 -8-- i need to be able to access to the parent project's fields, but I have a slight problem here. First off, I have to make the LEFT JOIN. I don't know if the project can be parent (therefore not finding a project_id = 0 wouldn't show the row) and I need to add some kind of prefix to the proj_parent's fields so that I can access them (or that they can't overwrite the project i'm getting info on. Well... there is an obvious sollution here: use an associative array instead of an object, and access the properties by doing $array[0], $array[1], etc. By my experience, this is a nightmare, maintenence-wise, so I'd only use it as a _LAST_ resource. Does anyone have any experience with this? The answer should be pretty obvious, but I can't seem to figure it out :( Thanks in advance. André -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL + PHP question
Hello, Consider this: tbl_project(id, name, parent) 1 6 / \ / \ 2 3 7 8 /\ 4 5 if tbl_project.parent = 0 then the project is the top parent. Therefore, 1 and 6 have the field parent = 0. So, say if you have project 5, do you want to find out its parent (2), or do you want to find out all its parents including grandparents (2) and (1)? What do you need to know? C. -Original Message- From: André Medeiros [mailto:[EMAIL PROTECTED] Sent: 26 July 2005 12:18 To: php-general@lists.php.net Subject: [PHP] MySQL + PHP question * This e-mail has been received by the Revenue Internet e-mail service. * Hi guys. I'm having some trouble here regarding a project. I have a table with projects, wich can be recursive (ie. sub-projects) and it is related to itself. By making the following query -8-- SELECT * FROM projects LEFT JOIN projects proj_parent ON projects.project_parent = proj_parent.parent_id WHERE project_id = 1234 -8-- i need to be able to access to the parent project's fields, but I have a slight problem here. First off, I have to make the LEFT JOIN. I don't know if the project can be parent (therefore not finding a project_id = 0 wouldn't show the row) and I need to add some kind of prefix to the proj_parent's fields so that I can access them (or that they can't overwrite the project i'm getting info on. Well... there is an obvious sollution here: use an associative array instead of an object, and access the properties by doing $array[0], $array[1], etc. By my experience, this is a nightmare, maintenence-wise, so I'd only use it as a _LAST_ resource. Does anyone have any experience with this? The answer should be pretty obvious, but I can't seem to figure it out :( Thanks in advance. André -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL + PHP question
On Tue, 2005-07-26 at 11:45 +0100, Shaw, Chris - Accenture wrote: Hello, Consider this: tbl_project(id, name, parent) 1 6 / \ / \ 2 3 7 8 /\ 4 5 if tbl_project.parent = 0 then the project is the top parent. Therefore, 1 and 6 have the field parent = 0. So, say if you have project 5, do you want to find out its parent (2), or do you want to find out all its parents including grandparents (2) and (1)? I just need to find it's parent. This is part of a permission system i'm building, and I need to somewhat register the objects in the permission system so I can manage things easilly from there. So to sum it up, this is my attempt to save queries :) What do you need to know? Projects can only be parent or children. No more recursiveness than that (it's possible, but we've implemented business rules that prevent it.) What I'm trying to do is to add some sort of prefix to the fields returned by the proj_parent table (wich is the projects table the same, but with different info, and will overwrite the object's properties). If not... heck, I'll have to make another query or use the associative array sollution (wich neither will make me very happy, but that's life). Thanks for your prompt reply :) C. -Original Message- From: André Medeiros [mailto:[EMAIL PROTECTED] Sent: 26 July 2005 12:18 To: php-general@lists.php.net Subject: [PHP] MySQL + PHP question * This e-mail has been received by the Revenue Internet e-mail service. * Hi guys. I'm having some trouble here regarding a project. I have a table with projects, wich can be recursive (ie. sub-projects) and it is related to itself. By making the following query -8-- SELECT * FROM projects LEFT JOIN projects proj_parent ON projects.project_parent = proj_parent.parent_id WHERE project_id = 1234 -8-- i need to be able to access to the parent project's fields, but I have a slight problem here. First off, I have to make the LEFT JOIN. I don't know if the project can be parent (therefore not finding a project_id = 0 wouldn't show the row) and I need to add some kind of prefix to the proj_parent's fields so that I can access them (or that they can't overwrite the project i'm getting info on. Well... there is an obvious sollution here: use an associative array instead of an object, and access the properties by doing $array[0], $array[1], etc. By my experience, this is a nightmare, maintenence-wise, so I'd only use it as a _LAST_ resource. Does anyone have any experience with this? The answer should be pretty obvious, but I can't seem to figure it out :( Thanks in advance. André -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL + PHP question
Are you familiar with Joe Celko's tree theory? It might help you understand more about the problem. http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427 -- Hello, Consider this: tbl_project(id, name, parent) 1 6 / \ / \ 2 3 7 8 /\ 4 5 if tbl_project.parent = 0 then the project is the top parent. Therefore, 1 and 6 have the field parent = 0. So, say if you have project 5, do you want to find out its parent (2), or do you want to find out all its parents including grandparents (2) and (1)? What do you need to know? C. -Original Message- From: André Medeiros [mailto:[EMAIL PROTECTED] Sent: 26 July 2005 12:18 To: php-general@lists.php.net Subject: [PHP] MySQL + PHP question Hi guys. I'm having some trouble here regarding a project. I have a table with projects, wich can be recursive (ie. sub-projects) and it is related to itself. By making the following query -8-- SELECT * FROM projects LEFT JOIN projects proj_parent ON projects.project_parent = proj_parent.parent_id WHERE project_id = 1234 -8-- i need to be able to access to the parent project's fields, but I have a slight problem here. First off, I have to make the LEFT JOIN. I don't know if the project can be parent (therefore not finding a project_id = 0 wouldn't show the row) and I need to add some kind of prefix to the proj_parent's fields so that I can access them (or that they can't overwrite the project i'm getting info on. Well... there is an obvious sollution here: use an associative array instead of an object, and access the properties by doing $array[0], $array[1], etc. By my experience, this is a nightmare, maintenence-wise, so I'd only use it as a _LAST_ resource. Does anyone have any experience with this? The answer should be pretty obvious, but I can't seem to figure it out :( Thanks in advance. André -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL + PHP question
Hello, Well its simply this: select b.id from tbl_project a, tbl_project b where b.id = a.parent and a.id = 5 This will return the id of project 5 parent, in this case it is 2. If the query doesn't return any rows then you know the project doesn't have a parent. I cannot test it on MySQL, but this query should work on MSSQL and Oracle. HTH. C. -Original Message- From: André Medeiros [mailto:[EMAIL PROTECTED] Sent: 26 July 2005 12:53 To: Shaw, Chris - Accenture Cc: php-general@lists.php.net Subject: RE: [PHP] MySQL + PHP question * This e-mail has been received by the Revenue Internet e-mail service. * On Tue, 2005-07-26 at 11:45 +0100, Shaw, Chris - Accenture wrote: Hello, Consider this: tbl_project(id, name, parent) 1 6 / \ / \ 2 3 7 8 /\ 4 5 if tbl_project.parent = 0 then the project is the top parent. Therefore, 1 and 6 have the field parent = 0. So, say if you have project 5, do you want to find out its parent (2), or do you want to find out all its parents including grandparents (2) and (1)? I just need to find it's parent. This is part of a permission system i'm building, and I need to somewhat register the objects in the permission system so I can manage things easilly from there. So to sum it up, this is my attempt to save queries :) What do you need to know? Projects can only be parent or children. No more recursiveness than that (it's possible, but we've implemented business rules that prevent it.) What I'm trying to do is to add some sort of prefix to the fields returned by the proj_parent table (wich is the projects table the same, but with different info, and will overwrite the object's properties). If not... heck, I'll have to make another query or use the associative array sollution (wich neither will make me very happy, but that's life). Thanks for your prompt reply :) C. -Original Message- From: André Medeiros [mailto:[EMAIL PROTECTED] Sent: 26 July 2005 12:18 To: php-general@lists.php.net Subject: [PHP] MySQL + PHP question * This e-mail has been received by the Revenue Internet e-mail service. * Hi guys. I'm having some trouble here regarding a project. I have a table with projects, wich can be recursive (ie. sub-projects) and it is related to itself. By making the following query -8-- SELECT * FROM projects LEFT JOIN projects proj_parent ON projects.project_parent = proj_parent.parent_id WHERE project_id = 1234 -8-- i need to be able to access to the parent project's fields, but I have a slight problem here. First off, I have to make the LEFT JOIN. I don't know if the project can be parent (therefore not finding a project_id = 0 wouldn't show the row) and I need to add some kind of prefix to the proj_parent's fields so that I can access them (or that they can't overwrite the project i'm getting info on. Well... there is an obvious sollution here: use an associative array instead of an object, and access the properties by doing $array[0], $array[1], etc. By my experience, this is a nightmare, maintenence-wise, so I'd only use it as a _LAST_ resource. Does anyone have any experience with this? The answer should be pretty obvious, but I can't seem to figure it out :( Thanks in advance. André -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php This message has been delivered to the Internet by the Revenue Internet e-mail service * This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL + PHP question
One thing I didn't quite explain myself well... I'm building this to register objects on a permission system. The SQL weight is heavy as it is, and I want to save queries as much as possible. Making two queries to extract information about a project and it's parent is not something I'd want to do. I know recursiveness, but thanks for the pointers and for the reply :) Best regards -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL + PHP question
You're missing the point here. I know how to get the parent project's info. I know recursiveness. I _JUST_ don't know how to get the current and parent project's info in just ONE QUERY (since I'm trying to save them because the sql weight is getting pretty heavy). -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL + PHP question
André Medeiros [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] One thing I didn't quite explain myself well... I'm building this to register objects on a permission system. The SQL weight is heavy as it is, and I want to save queries as much as possible. Making two queries to extract information about a project and it's parent is not something I'd want to do. I know recursiveness, but thanks for the pointers and for the reply :) Best regards I must admit I still don't really get what you are looking for. Does this query help? select c.field1 AS childfield1, c.field2 AS childfield2, (etc) p.field1 AS parentfield1, p.field2 AS parentfield2, (etc) FROM child AS c LEFT JOIN parent AS p ON c.parent=p.id ? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL + PHP question
On Tue, 2005-07-26 at 13:39 +0100, Mark Rees wrote: André Medeiros [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] One thing I didn't quite explain myself well... I'm building this to register objects on a permission system. The SQL weight is heavy as it is, and I want to save queries as much as possible. Making two queries to extract information about a project and it's parent is not something I'd want to do. I know recursiveness, but thanks for the pointers and for the reply :) Best regards I must admit I still don't really get what you are looking for. Does this query help? select c.field1 AS childfield1, c.field2 AS childfield2, (etc) p.field1 AS parentfield1, p.field2 AS parentfield2, (etc) FROM child AS c LEFT JOIN parent AS p ON c.parent=p.id ? Yeah, that might do, thanks! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL + PHP question
Yeah I understand what he wants, but the problem boils down to the project with no parent. The only thing I can suggest is if you have a dummy row in, so the top parent row, (1) or (6) in my example with have the dummy as its parent. Then using: select b.id parentID, b.name parentName, a.id childID, b.name childName from tbl_project a, tbl_project b where b.id = a.parent and a.id = 1 Should give a row of: ParentID: 0 ParentName: Dummy ChildID: 1 ChildName: Project One So, if a Project has a parent that the name is called Dummy, then you know it doesn't have a parent. HTH. C. -Original Message- From: Mark Rees [mailto:[EMAIL PROTECTED] Sent: 26 July 2005 13:39 To: php-general@lists.php.net Subject: Re: [PHP] MySQL + PHP question * This e-mail has been received by the Revenue Internet e-mail service. * André Medeiros [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] One thing I didn't quite explain myself well... I'm building this to register objects on a permission system. The SQL weight is heavy as it is, and I want to save queries as much as possible. Making two queries to extract information about a project and it's parent is not something I'd want to do. I know recursiveness, but thanks for the pointers and for the reply :) Best regards I must admit I still don't really get what you are looking for. Does this query help? select c.field1 AS childfield1, c.field2 AS childfield2, (etc) p.field1 AS parentfield1, p.field2 AS parentfield2, (etc) FROM child AS c LEFT JOIN parent AS p ON c.parent=p.id ? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL PHP issue?
OK, I have check all the mysql doc and php docs on using exists in the query, but it seems I still can not get the query to work. Here it is: SELECT * FROM icisp.account WHERE id in (select oldaccountID from BillMax.account) Or Using sELECT * FROM icisp.account WHERE id exists(select oldaccountID from BillMax.account) Anyone have any idea what I'm doing wrong? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.859 / Virus Database: 585 - Release Date: 2/14/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL PHP issue?
[snip] OK, I have check all the mysql doc and php docs on using exists in the query, but it seems I still can not get the query to work. Here it is: SELECT * FROM icisp.account WHERE id in (select oldaccountID from BillMax.account) Or Using sELECT * FROM icisp.account WHERE id exists(select oldaccountID from BillMax.account) Anyone have any idea what I'm doing wrong? [/snip] Asking a MySQL question on a PHP list? *slaps knee!* What version of MySQL are you running, does it allow sub queries? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL PHP issue?
Dustin Wish wrote: snip Anyone have any idea what I'm doing wrong? /snip Asking on the wrong list. -- 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 PHP issue?
Jay Blanchard wrote: snip Asking a MySQL question on a PHP list? *slaps knee!* /snip I bet you think you're funny. ;) -- 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 PHP issue?
[snip] Jay Blanchard wrote: snip Asking a MySQL question on a PHP list? *slaps knee!* /snip I bet you think you're funny. ;) [/snip] I know that you think I am. Yuk-yuk! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL PHP issue?
It is version 3.23.56 -Original Message- From: John Nichel [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 2:44 PM To: php-general@lists.php.net Subject: Re: [PHP] MySQL PHP issue? Jay Blanchard wrote: snip Asking a MySQL question on a PHP list? *slaps knee!* /snip I bet you think you're funny. ;) -- 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 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.859 / Virus Database: 585 - Release Date: 2/14/2005 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.859 / Virus Database: 585 - Release Date: 2/14/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL PHP issue?
[snip] It is version 3.23.56 [/snip] Sub-queries are not supported in that version of MySQL. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL PHP issue?
SH^*^t Thanks guys. Figured something like that. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 27, 2005 2:53 PM To: Dustin Wish; php-general@lists.php.net Subject: RE: [PHP] MySQL PHP issue? [snip] It is version 3.23.56 [/snip] Sub-queries are not supported in that version of MySQL. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.859 / Virus Database: 585 - Release Date: 2/14/2005 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.859 / Virus Database: 585 - Release Date: 2/14/2005 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL PHP issue?
Dustin Wish wrote: It is version 3.23.56 snip Know thy software. 3.x doesn't support sub-queries. -- 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, PHP and JBOSS
david joffrin wrote: Hi, I finally managed to run JBOSS/Tomcat with PHP, however, I am facing the following error message when my PHP code is accessing MySQL: Fatal error: Call to undefined function mysql_connect() in C:\APPS\jboss-3.2.6\server\sudetp\deploy\jbossweb-tomcat50.sar\ROOT.war\registration.php on line 87. However, the same code runs successfully on Apache2! Any configuration that I am missing somewhere? run phpinfo(), it will tell you what php.ini file it's using -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL, PHP and JBOSS
Hi, I finally managed to run JBOSS/Tomcat with PHP, however, I am facing the following error message when my PHP code is accessing MySQL: Fatal error: Call to undefined function mysql_connect() in C:\APPS\jboss-3.2.6\server\sudetp\deploy\jbossweb-tomcat50.sar\ROOT.war\registration.php on line 87. However, the same code runs successfully on Apache2! Any configuration that I am missing somewhere? Thanks. DvJ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL - PHP - limits...
Alex Ortiz wrote: Hello. Is there a records limit when browsing (a sql SELECT) in PHP from a MySQL table? no limit on php side, perhaps 2^31 if you do calculations. I´m browsing a table with PHP from MySQL, and something is wrong, if the table has more than 5047 records, the browse appears blank. Additionaly, if make the browse in a child window (target=_blank), the limit becomes lower: 437 records. My SQL query is: SELECT * FROM my_table ORDER BY my_order_index The way I solve the problem is adding LIMIT 5047 to query (or 437), just for testing. This works fine. Echo out your sql query and see if you get what you should. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL - PHP - limits...
Is there a records limit when browsing (a sql SELECT) in PHP from a MySQL table? Sort of, I think... When MySQL is compiled, there is a buffer limit on how much crap you can squirt through a single connection. You can maybe alter this in my.cnf or be re-compiling MySQL. If you are trying to push *THAT* much data through, though, you are probably doing something fundamentally wrong in web design... Okay, maybe for some kind of admin or debug or single-user screen... Nah, just use the mysql monitor for that. I´m browsing a table with PHP from MySQL, and something is wrong, if the table has more than 5047 records, the browse appears blank. Additionaly, if make the browse in a child window (target=_blank), the limit becomes lower: 437 records. Your numbers make me suspect that you are hitting a buffer limit, rather than anything else. Change the select (temporarily) to get a *LOT* less columns and see if the limit changes. If so, you can be pretty sure it's a buffer limit. PS If the browser appears blank, you haven't written any good MySQL/PHP error checking. Go do that *NOW*. It will probably tell you *exactly* what went wrong. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL - PHP - limits...
Hello. Is there a records limit when browsing (a sql SELECT) in PHP from a MySQL table? I´m browsing a table with PHP from MySQL, and something is wrong, if the table has more than 5047 records, the browse appears blank. Additionaly, if make the browse in a child window (target=_blank), the limit becomes lower: 437 records. My SQL query is: SELECT * FROM my_table ORDER BY my_order_index The way I solve the problem is adding LIMIT 5047 to query (or 437), just for testing. This works fine. My table has 11300 records, with 9 small fields (average 10 bytes each). I run SuSE Linux 8.2, Apache2.0, PHP 4.3.3, and MySQL 4.0.15. Any idea?. Thanks in advance, Alex Ortiz -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL PHP database script PLEASE
td? echo $row[end]; ?/td
tdA HREF=/momatlib/gendai/pdf/? echo $row[GO]; ?.pdfpdf/A/td
/tr
replace the above block with
td? echo $row[end]; ?/td
td
?php if($row[GO] = 1000) { ?
A HREF=/momatlib/gendai/pdf/? echo $row[GO]; ?.pdfpdf/A
?php }?
/td
/tr
ganbatte!
ramil
http://ramil.sagum.net
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL PHP database script PLEASE
snip
I NEED THIS
When GO = 1000 ), the links to PDF file are shown,
and when GO 1000, the links to PDF file are not shown (just brank
cells).
So probably I need to change this part.
tdA HREF=/momatlib/gendai/pdf/? echo $row[GO];
?.pdfpdf/A/td
But dont know how to do it.
Please help me anybody, thank you!!
My syntax may look different but it's basically the same thing.
Here is a complete PHP (simplified version) Im using.
--
html
body
TITLEgen_search1.php/TITLE
BODY TEXT=#00 LINK=#FF VLINK=#008040 ALINK=#FF00FF
BASEFONT SIZE=3
table border=1 align=center BGCOLOR=#F2FEE0
tr
td align=centerID/td
td align=centerVolume/td
td align=centerDate/td
td align=centerTitle/td
td align=centerAuthor/td
td align=centerPage/td
td align=centerPage/td
td align=centerImage/td
/tr
?
mysql_connect(localhost,root,love);
// I hope that's not your real passwd that you just posted. :)
mysql_select_db(gendai);
if($go ==
$dt ==
$ti ==
$au ==
$ay == )
{
echo 'Please type something';
}
/*
From the looks of the code below, these ought to be $_GET vars? Where are
they coming from? When passing variables through the URL they should be
called through the $_GET array. Variables passed through headers are
called through the $_POST vars. I'll use $_GET as I'm simpy guessing.
This also might mean that you have register_globals on in php.ini? You
should turn it off and train to use the $_GET and $_POST.
http://us2.php.net/manual/en/language.variables.external.php
http://us2.php.net/manual/en/security.registerglobals.php
*/
if (empty($_GET[go])
empty($_GET[dt])
empty($_GET[ti])
empty($_GET[au])
empty($_GET[ay])) {
echo Please type something;
elseif($go == % || $dt == % || $ti == % || $au == % || $ay ==
%){
echo 'Not Valid';
}
} elseif ($_GET[go] == % |
$_GET[dt] == % |
$_GET[ti] == % |
$_GET[au] == % |
$_GET[ay] == %) {
echo Not Valid;
else{
if($go == ){
$go = '%';
}
if($dt == ){
$dt = '%';
}
if($ti == ){
$ti = '%';
}
if($au == ){
$au = '%';
}
if($ay == ){
$ay = '%';
}
// For this part we can make it a little easier using a foreach loop.
} else {
foreach ($_GET as $value) {
if (empty($value)) $value = %;
}
/*
The foreach loop will cycle through each of the values in the $_GET array
and assign it a % if it's empty
*/
$result = mysql_query(select * from gen_table
where GO like '%$go%'
and ym like '%$dt%'
and Tit like '%$ti%'
and Aut like '%$au%'
and Auty like '%$ay%');
// What I think you're doing is using the $_GET array to query your DB?
$result = mysql_query(select * from gen_table
where GO like ''.$_GET[go].''
and ym like ''.$_GET[dt].''
and Tit like ''.$_GET[ti.''
and Aut like ''.$_GET[au].''
and Auty like ''.$_GET[ay].');
/*
Because $_GET is an array you need to concatenate them into the string.
Check out complex (curly) syntax:
http://www.php.net/manual/en/language.types.string.php#language.types.string.parsing.complex
*/
$rows = mysql_num_rows($result);
echo $rows,Records Availablep;
// %row is just a straight variable so you can include it in the string
echo $rows Records Availablep;
while($row = mysql_fetch_array($result)){
?
tr
td align =rightA HREF = gen_detail.php ?id=? echo $row[ID]
??
echo $row[ID]; /A/td
td align =rightA HREF = gen_search1.php ?go=? echo $row[GO]
?? echo $row[GO]; /A/td
td? echo $row[ym]; ?/td
td width=400? echo $row[Tit]; ?/td
td width=200? echo $row[Aut]; ?/td
td? echo $row[sta]; ?/td
td? echo $row[end]; ?/td
tdA HREF=/momatlib/gendai/pdf/? echo $row[GO];
?.pdfpdf/A/td
Were the above $row[ID] and $row[GO] missing the semi-colon a typo?
/tr
?
}
}
?
/table
/body
/html
--
Not sure if I missed anything?
--
--Matthew Sims
--http://killermookie.org
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL PHP Examples Training Providers Required
Robby Russell wrote: Not to start a SQL war, but have you consider PostgreSQL as well in your decisions? And how about Firebird, Oracle has roots from the same original development in the 1980's. -- Lester Caine - L.S.Caine Electronic Services -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL PHP Examples Training Providers Required
[snip] I'm looking for some examples of sites that are purely MySQL and PHP running on Unix and that contain a few thousand records preferably held in relational databases. Rationale: I need to justify PHP as a tool of choice over say vb.net or Oracle. My recommendation, despite my limited knowledge of MySQL and PHP is that even if we have 10-15 databases holding upwards of 10,000 records each PHP and MySQL are the tools of choice and I doubt that there are any functions missing that you'd find in VB.Net. I could be wrong and if so, please let me know. [/snip] We do not have any external sites that can be looked at, but we have several internal apps on Linux and BSD boxes, all running MySQL and PHP. Our largest application processes an average of 1.2 million records per day with the database currently holding nearly 350 million (350,000,000) records. One of the tables in this database contains 180,000,000+ records. Each of the tables in this particular database are configured to be able to hold 1 billion records if required. (DON'T FORGET TO PROPERLY INDEX LARGE TABLES!) We use PHP to process those records for use in various systems throughout the company as well as providing reporting mechanisms on those records. PHP has proven to be a reliable and effective tool for creating applications where ditribution to hundreds of users is required and rapid roll out is a necessity. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL PHP Examples Training Providers Required
Hi all. This might sound like a strange request but here goes. I'm looking for some examples of sites that are purely MySQL and PHP running on Unix and that contain a few thousand records preferably held in relational databases. Rationale: I need to justify PHP as a tool of choice over say vb.net or Oracle. My recommendation, despite my limited knowledge of MySQL and PHP is that even if we have 10-15 databases holding upwards of 10,000 records each PHP and MySQL are the tools of choice and I doubt that there are any functions missing that you'd find in VB.Net. I could be wrong and if so, please let me know. My other question is that I am looking for training in the UK, preferably in the North. I have no idea about accreditation or certification requirements and wondered if anyone could provide any recommendations...? Thanks for your time guys. -- - Michael Mason Arras People www.arraspeople.co.uk - -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL PHP Examples Training Providers Required
Not to start a SQL war, but have you consider PostgreSQL as well in your decisions? PostgreSQL has more features that are more comparable to MSSQL and Oracle. Just making sure you make the right decision. ;-) -Robby On Tue, 2004-08-10 at 16:06, Harlequin wrote: Hi all. This might sound like a strange request but here goes. I'm looking for some examples of sites that are purely MySQL and PHP running on Unix and that contain a few thousand records preferably held in relational databases. Rationale: I need to justify PHP as a tool of choice over say vb.net or Oracle. My recommendation, despite my limited knowledge of MySQL and PHP is that even if we have 10-15 databases holding upwards of 10,000 records each PHP and MySQL are the tools of choice and I doubt that there are any functions missing that you'd find in VB.Net. I could be wrong and if so, please let me know. My other question is that I am looking for training in the UK, preferably in the North. I have no idea about accreditation or certification requirements and wondered if anyone could provide any recommendations...? Thanks for your time guys. -- - Michael Mason Arras People www.arraspeople.co.uk - -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development / signature.asc Description: This is a digitally signed message part
Re: [PHP] MySQL/PHP Tunneling
On Sunday 11 July 2004 13:07, Karam Chand wrote: Sorry. But I just didnt remember the email addy so I took that way :). Can't resist picking on this one. All posts to the list should/would have the list address in the To: header. It's not that hard to copy paste the address into a *new* mail. Anyway just add the list address into your address book. NOW :) Now, many of the ISPs blokc 3306 for security reason and you cannot access MySQL from a 3rd party tool and have to use phpMyAdmin which is able to access the MySQL server as it is running on the same box. Sometimes, SSH tunneling is also not the option :) Most of these tools use MySQL C API() or some sort of wrapper for it to connnect to the server and do their job. Instead of connecting directly to the server using: mysql_real_connect ( ). They callup the above mentioned PHP file and pass the query as a argument. The PHP file then connects to the local mysql server,executes the query and returns all the required data as XML or a pre-determined format. In the client side the app again assembles this data and fills up MYSQL_RES* structure, the main structure in C API() to work with resultsets. Just a few points I want to bring up: 1) Any monkey can run queries on your server unless you authenticate the connection using sessions. 2) Your data will pass through the web in clear text unless you do your own encryption/decryption or use SSL. 3) Adding an access layer would obviously increase the overhead. If this is for a mission-critical application then you really are much better off getting yourself a dedicated server then you can do whatever tunnelling you want. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-general -- /* May the forces of evil become confused on the way to your house. -- George Carlin */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL/PHP Tunneling
* Thus wrote Karam Chand: --- Curt Zirzow [EMAIL PROTECTED] wrote: Now, many of the ISPs blokc 3306 for security reason and you cannot access MySQL from a 3rd party tool and have to use phpMyAdmin which is able to access the MySQL server as it is running on the same box. Sometimes, SSH tunneling is also not the option :) There usually is a reason why the port 3306 port is blocked or that mysql simply doesn't listen to outside addresses on that port. By trying to circumstant that will probably result in a violation of their TOS. And as Jason suggested, use an ISP that either supports ssh tunneling (which is preferred and more likely to occur) or find a ISP that allows port 3336 to the open world. What you describe is more in the lines of Proxying, which is probably why I was confused. And I wouldn't suggest to anyone to do that kind of proxying, the layers between everything can be *very* unstable. Curt -- First, let me assure you that this is not one of those shady pyramid schemes you've been hearing about. No, sir. Our model is the trapezoid! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL/PHP Tunneling
Hello, Well, I know there are issues regarding this (security as well as others). But tools like MySQL-Front and DBTools are just doing that and it just happens that the project i am working on needed something like that, so I was just asking :) Regards, Karam --- Curt Zirzow [EMAIL PROTECTED] wrote: * Thus wrote Karam Chand: --- Curt Zirzow [EMAIL PROTECTED] wrote: Now, many of the ISPs blokc 3306 for security reason and you cannot access MySQL from a 3rd party tool and have to use phpMyAdmin which is able to access the MySQL server as it is running on the same box. Sometimes, SSH tunneling is also not the option :) There usually is a reason why the port 3306 port is blocked or that mysql simply doesn't listen to outside addresses on that port. By trying to circumstant that will probably result in a violation of their TOS. And as Jason suggested, use an ISP that either supports ssh tunneling (which is preferred and more likely to occur) or find a ISP that allows port 3336 to the open world. What you describe is more in the lines of Proxying, which is probably why I was confused. And I wouldn't suggest to anyone to do that kind of proxying, the layers between everything can be *very* unstable. Curt -- First, let me assure you that this is not one of those shady pyramid schemes you've been hearing about. No, sir. Our model is the trapezoid! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL/PHP Tunneling
Hello, Recently lot of MySQL clients try to overcome host based privilege system of MySQL by using PHP tunneling method. In this method they call up a PHP file in the server and the PHP file executes a query and sends the data in XML format. I am using C API() and I was just wondering if somebody is working on such tunnels i.e. a PHP file and its corresponding C/++ code that will fill up MYSQL_RES structures correctly so that I can use them to C API() without any problem. Otherwise, i guess i have to write one for myself. Regards, Karam __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL/PHP Tunneling
First off, when starting a new topic, don't reply to message and then change the topic. * Thus wrote Karam Chand: Hello, Recently lot of MySQL clients try to overcome host based privilege system of MySQL by using PHP tunneling method. In this method they call up a PHP file in the server and the PHP file executes a query and sends the data in XML format. This doesn't really explain much. I am using C API() and I was just wondering if somebody is working on such tunnels i.e. a PHP file and its corresponding C/++ code that will fill up MYSQL_RES structures correctly so that I can use them to C API() without any problem. Otherwise, i guess i have to write one for myself. Now, I'm utterly confused. I have no idea what you're referring to as C API() nor php tunneling. Curt -- First, let me assure you that this is not one of those shady pyramid schemes you've been hearing about. No, sir. Our model is the trapezoid! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL/PHP Tunneling
--- Curt Zirzow [EMAIL PROTECTED] wrote: First off, when starting a new topic, don't reply to message and then change the topic. Sorry. But I just didnt remember the email addy so I took that way :). * Thus wrote Karam Chand: Hello, Recently lot of MySQL clients try to overcome host based privilege system of MySQL by using PHP tunneling method. In this method they call up a PHP file in the server and the PHP file executes a query and sends the data in XML format. This doesn't really explain much. Try out www.mysqlfront.de (docs should be enough i guess :) ). Its a GUI for MySQL. It has an option for PHP tunneling and this is what I am refering to. Now, many of the ISPs blokc 3306 for security reason and you cannot access MySQL from a 3rd party tool and have to use phpMyAdmin which is able to access the MySQL server as it is running on the same box. Sometimes, SSH tunneling is also not the option :) Most of these tools use MySQL C API() or some sort of wrapper for it to connnect to the server and do their job. Instead of connecting directly to the server using: mysql_real_connect ( ). They callup the above mentioned PHP file and pass the query as a argument. The PHP file then connects to the local mysql server,executes the query and returns all the required data as XML or a pre-determined format. In the client side the app again assembles this data and fills up MYSQL_RES* structure, the main structure in C API() to work with resultsets. In short this is what happens: /* connect to the php file using your C prog. e.g. http://somehost.com/some.php?query=select * from sometable the PHP file then executes the query and writes the result as XML (for e.g.). result row col1aaa/col1 col2asasas/col2 ... /row row /row /result in the client side the app fills creates and fills up the MYSQL_RES sturcture with this data and then use various APIs like: mysql_fetch_row() mysql_fetch_fields() */ So basically instead of working on raw sockets and using MySQL protocol to fill up the required sturctures, they make a bridge between HTTP - MySQL protocol. So I just wanted to know if somebody has written a generic library like this? Otherwise, I guess I have to write one for my app. Regards, Karam I am using C API() and I was just wondering if somebody is working on such tunnels i.e. a PHP file and its corresponding C/++ code that will fill up MYSQL_RES structures correctly so that I can use them to C API() without any problem. Otherwise, i guess i have to write one for myself. Now, I'm utterly confused. I have no idea what you're referring to as C API() nor php tunneling. Curt -- First, let me assure you that this is not one of those shady pyramid schemes you've been hearing about. No, sir. Our model is the trapezoid! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL PHP
I've just installed Fedora Core 2, including I believe both PHP MySQL.. However whenever I try and run something that connects to MySQL, it comes up with the following: cannot load MySQL extension, please check PHP Configuration. Does this mean anything to anyone? I've checked loads of things, like MySQL is running, etc etc But without luck.. Cheers Nunners
RE: [PHP] MySQL PHP
Looks like the mysql module for php is not loaded. Is extension=mysql.so included in the php.ini. Nunners wrote on vrijdag 4 juni 2004 14:07: I've just installed Fedora Core 2, including I believe both PHP MySQL.. However whenever I try and run something that connects to MySQL, it comes up with the following: cannot load MySQL extension, please check PHP Configuration. Does this mean anything to anyone? I've checked loads of things, like MySQL is running, etc etc But without luck.. Cheers Nunners -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL PHP
Run: rpm -q php-mysql From the command line as root and see if it is there, if not go out and download the fedora rpm with that in the title, since that would be the missing piece. HTH, Robert -Original Message- From: Nunners [mailto:[EMAIL PROTECTED] Sent: Friday, June 04, 2004 8:07 AM To: [EMAIL PROTECTED] Subject: [PHP] MySQL PHP I've just installed Fedora Core 2, including I believe both PHP MySQL.. However whenever I try and run something that connects to MySQL, it comes up with the following: cannot load MySQL extension, please check PHP Configuration. Does this mean anything to anyone? I've checked loads of things, like MySQL is running, etc etc But without luck.. Cheers Nunners -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL/PHP problem.
* Thus wrote Phillip Blancher ([EMAIL PROTECTED]):
I am trying to count in mySQL the number of entries in the field day where day=2
or 3.
Then I want to check just to see if that returned a value greater than 0 or not.
I am using the code below, but having a problem, I keep getting 0 as the total
What am i doing wrong.
$dbqueryshipping1 = select *, COUNT(day) from tempuserpurchase where day=\2\
and day=\3\ GROUP BY itemname;
$resultshipping1 = mysql_db_query($dbname,$dbqueryshipping1);
if(mysql_error()!=){echo mysql_error();}
$shipping1 = mysql_fetch_array($resultshipping1);
try a print_r($shipping1); I'll bet your value is in there just you
arn't accessing it.
It would help if you supplied your code for trying to access your total.
Curt
--
I used to think I was indecisive, but now I'm not so sure.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL/PHP problem.
I tried using the print_r for $shipping. - All i want this to do is to check
to see what days in the grouping is there, and if there is anything for a
grouping, charge $5 for the delivery. If there are items in two of the
groupings, charge $10 and if there is in all three groupings, charge $15.
This is what print_r has returned.
Array ( [0] = 4 [day] = 4 [1] = 4 [COUNT(*)] = 4 ) 11Array ( [0] = 1
[day] = 1 [1] = 9 [COUNT(*)] = 9 ) 1amount from DB query10
This is the code I have done, showing the print_r's for Shipping 1, 2 and 3.
?
$dbqueryshipping1 = select day, COUNT(*) from tempuserpurchase where
day=\2\ or day=\3\ GROUP BY day;
$resultshipping1 = mysql_db_query($dbname,$dbqueryshipping1);
if(mysql_error()!=){echo mysql_error();}
$shipping1 = mysql_fetch_array($resultshipping1);
$dbqueryshipping2 = select day, COUNT(*) from tempuserpurchase where
day=\4\ or day=\5\ GROUP BY day;
$resultshipping2 = mysql_db_query($dbname,$dbqueryshipping2);
if(mysql_error()!=){echo mysql_error();}
$shipping1 = mysql_fetch_array($resultshipping2);
$dbqueryshipping3 = select day, COUNT(*) from tempuserpurchase where
day=\6\ or day=\7\ or day=\1\ GROUP BY day;
$resultshipping3 = mysql_db_query($dbname,$dbqueryshipping3);
if(mysql_error()!=){echo mysql_error();}
$shipping3 = mysql_fetch_array($resultshipping3);
if(($shipping1 ) and ($shipping2 ) and ($shipping3 ))
{ $shipping=15;}
elseif((($shipping1 ) and ($shipping2 )) or (($shipping2
) and ($shipping3 )) or (($shipping1 ) and ($shipping3 )))
{ $shipping=10;}
elseif(($shipping1 ) or ($shipping2 ) or ($shipping3 ))
{ $shipping=5;}
else{ $shipping=0;}
echo print_r($shipping1); echo print_r($shipping2); echo
print_r($shipping3);
echo amount from DB query; echo $shipping;
?
- Original Message -
From: Curt Zirzow [EMAIL PROTECTED]
To: PHP List [EMAIL PROTECTED]
Sent: Tuesday, July 22, 2003 5:23 PM
Subject: Re: [PHP] MySQL/PHP problem.
* Thus wrote Phillip Blancher ([EMAIL PROTECTED]):
I am trying to count in mySQL the number of entries in the field day
where day=2 or 3.
Then I want to check just to see if that returned a value greater than 0
or not.
I am using the code below, but having a problem, I keep getting 0 as the
total
What am i doing wrong.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 7/14/2003
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL/PHP problem.
On Tue, 22 Jul 2003 17:08:50 -0400, you wrote:
I am trying to count in mySQL the number of entries in the field day where day=2 or
3.
Then I want to check just to see if that returned a value greater than 0 or not.
I am using the code below, but having a problem, I keep getting 0 as the total
What am i doing wrong.
$dbqueryshipping1 = select *, COUNT(day) from tempuserpurchase where day=\2\
and day=\3\ GROUP BY itemname;
$resultshipping1 = mysql_db_query($dbname,$dbqueryshipping1);
if(mysql_error()!=){echo mysql_error();}
$shipping1 = mysql_fetch_array($resultshipping1);
You essentially are looking for TRUE or FALSE, right? Nothing else?
a) What's the * for?
b) day can never be 2 AND 3 at the same time. Boolean and/or usage is more
strict than English and/or usage.
Try this
function x() {
$query = SELECT COUNT(*) FROM tempuserpurchase WHERE day=2 OR day=3;
[...]
$a = mysql_fetch_row($r);
if ($a[0]) {
return (TRUE);
}
return (FALSE);
}
That should give you the core of a function that returns TRUE if there are
rows in the db where day = 2 or day = 3. Expand as you wish (eg moving the
magic numbers out of the sql query).
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL/PHP problem.
Off the top of my head, try using OR in your query rather than AND
day=\2\ or day=\3\
day probably won't equal both 2 and 3, which is why you are getting 0.
Also, if you group by item name, you are going to get the total for each
itemname, is that what you want, or just the overall total?
-Original Message-
From: Phillip Blancher [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 22, 2003 2:09 PM
To: PHP List
Subject: [PHP] MySQL/PHP problem.
I am trying to count in mySQL the number of entries in the field
day where day=2 or 3.
Then I want to check just to see if that returned a value greater
than 0 or not.
I am using the code below, but having a problem, I keep getting 0
as the total
What am i doing wrong.
$dbqueryshipping1 = select *, COUNT(day) from
tempuserpurchase where day=\2\ and day=\3\ GROUP BY itemname;
$resultshipping1 =
mysql_db_query($dbname,$dbqueryshipping1);
if(mysql_error()!=){echo mysql_error();}
$shipping1 = mysql_fetch_array($resultshipping1);
Thanks in advance,
Phil
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 7/14/2003
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL/PHP problem.
I am trying to count in mySQL the number of entries in the field day where day=2 or
3.
Then I want to check just to see if that returned a value greater than 0 or not.
I am using the code below, but having a problem, I keep getting 0 as the total
What am i doing wrong.
$dbqueryshipping1 = select *, COUNT(day) from tempuserpurchase where day=\2\ and
day=\3\ GROUP BY itemname;
$resultshipping1 = mysql_db_query($dbname,$dbqueryshipping1);
if(mysql_error()!=){echo mysql_error();}
$shipping1 = mysql_fetch_array($resultshipping1);
Thanks in advance,
Phil
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.501 / Virus Database: 299 - Release Date: 7/14/2003
Re: [PHP] mysql/php query - wrox
Chris Shiflett wrote in message: Your query returns two rows because you have two rows in the database that match the condition: host='localhost' -- Chris .. Chris, I was under the impression that the mysql database had as default user when installed: .. anonymous root And that the _anonymous_ user could only create a database that began with the word - test. Using the following commands: ... C:\WINDOWS\cd c:\mysql\bin C:\mysql-shareware --standalone C:\mysql\binmysqladmin CREATE houses Database houses created C:\mysql\binmysql Welcome to the mysql monitor... mysqlUSE houses Database changed As it is, I'm reading Meloni's tutorial on mysql/PHP so the question might also apply to others in this PHP ng. Anyway, the database houses was created _without_ the word test as the anonymous user. Why is this? Thanking you, Tony Ritter -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL/PHP Associative Array Insert
- Original Message -
From: CDitty [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, February 01, 2003 11:26 AM
Subject: [PHP] MySQL/PHP Associative Array Insert
A co-worker is teaching me to move to the next level in php. I have
started using associative arrays for my scripts, but I am having a problem
using them to do an insert into MySQL. Can someone give me an example of
how to do an insert to the database using these arrays? My array is
this...$item['itemID']
The trick is to wrap array references in curly braces inside double quoted
strings.
// some data
$array['one'] = 'one';
$array['two'] = 'two';
// build the sql
$sql = INSERT into table (field1,field2)
values('{$array['one']}','{array['two']}';
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql/php query - wrox
Working on: Win 98 / mysql 3.23.55 / php 4 I installed mysql 3.23.55 and wanted to check the user table in the mysql db. The query was: SELECT User FROM user WHERE host='localhost'; The output was: xx user | xx || || root | xx 2 rows in set . In the Wrox book on PHP (page 396) by running that query it is: x--x user | x--x root | x-x 1 row in set I'm not sure why I'm getting two rows. Many thanks for your assistance. TR -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql/php query - wrox
--- Anthony Ritter wrote: I installed mysql 3.23.55 and wanted to check the user table in the mysql db. The query was: SELECT User FROM user WHERE host='localhost'; This is a PHP list. There are mailing lists for MySQL that you should check out if you are having trouble understanding databases. Your query returns two rows because you have two rows in the database that match the condition: host='localhost' -- Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL/PHP Associative Array Insert
A co-worker is teaching me to move to the next level in php. I have started using associative arrays for my scripts, but I am having a problem using them to do an insert into MySQL. Can someone give me an example of how to do an insert to the database using these arrays? My array is this...$item['itemID'] Thanks Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL/PHP Associative Array Insert
Yes Chris, When you add the item to the database, make sure to leave out the single quotes inside the brackets of the associative array. For example: Instead Of: $item['itemID'] Do This: $item[itemID] Example Query: $query = SELECT * FROM items WHERE '$item[itemID]'; Hope that helps. -Original Message- From: CDitty [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 01, 2003 10:26 AM To: [EMAIL PROTECTED] Subject: [PHP] MySQL/PHP Associative Array Insert A co-worker is teaching me to move to the next level in php. I have started using associative arrays for my scripts, but I am having a problem using them to do an insert into MySQL. Can someone give me an example of how to do an insert to the database using these arrays? My array is this...$item['itemID'] Thanks Chris -- 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/PHP Associative Array Insert
Oops, I forgot the double quotes at the end of the example query from the last email. This is how it should be: Example Query: $query = SELECT * FROM items WHERE '$item[itemID]'; -Original Message- From: @ Nilaab [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 01, 2003 1:00 PM To: [EMAIL PROTECTED] Subject: RE: [PHP] MySQL/PHP Associative Array Insert Yes Chris, When you add the item to the database, make sure to leave out the single quotes inside the brackets of the associative array. For example: Instead Of: $item['itemID'] Do This: $item[itemID] Example Query: $query = SELECT * FROM items WHERE '$item[itemID]'; Hope that helps. -Original Message- From: CDitty [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 01, 2003 10:26 AM To: [EMAIL PROTECTED] Subject: [PHP] MySQL/PHP Associative Array Insert A co-worker is teaching me to move to the next level in php. I have started using associative arrays for my scripts, but I am having a problem using them to do an insert into MySQL. Can someone give me an example of how to do an insert to the database using these arrays? My array is this...$item['itemID'] Thanks Chris -- 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Mysql/php database performance question
Hi, I got a question about using Mysql databases. I load textdata in VARCHAR colums up to size 50. I have about 5 of those columns. The last columns often contain empty cells. (data are wordmeanings, many words have only a 1 or 2meanings) What would be faster/better: - putting everything in a big varchar column (size 5x50) and PHP parsing them by comma after fetching or - keeping those 5 columns with a lot of empty cells in the last columns? Thanks, Simon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql/php database performance question
the thing that may make difference in performance (as i think) is whether you make it a fixed -CHAR- or a variable -VARCHAR- it's preferable that you make separate char columns, so that PHP will not have to explode every record! Regards, Khalid Al-Kary, Hi, I got a question about using Mysql databases. I load textdata in VARCHAR colums up to size 50. I have about 5 of those columns. The last columns often contain empty cells. (data are wordmeanings, many words have only a 1 or 2meanings) What would be faster/better: - putting everything in a big varchar column (size 5x50) and PHP parsing them by comma after fetching or - keeping those 5 columns with a lot of empty cells in the last columns? Thanks, Simon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ The new MSN 8 is here: Try it free* for 2 months http://join.msn.com/?page=dept/dialup -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql/php database performance question
I see what you mean Khalid, but I only retrieve 1 entry at the time, so exploding wouldn't be the biggest problem I suppose. Furthermore, if I use char columns, and some of those columns have lots of empty cells, isn't it a waste of space/lookup-time? So I think I have to reformulate the question: which is better 5 varchar columns of size 50 or 1 varchar column of size 250 (regardless of parsing). Thanks, Simon --- the thing that may make difference in performance (as i think) is whether you make it a fixed -CHAR- or a variable -VARCHAR- it's preferable that you make separate char columns, so that PHP will not have to explode every record! Regards, Khalid Al-Kary, -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql/php database performance question
-Original Message- From: Simon Dedeyne [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 9:03 AM To: [EMAIL PROTECTED] Subject: RE: [PHP] Mysql/php database performance question So I think I have to reformulate the question: which is better 5 varchar columns of size 50 or 1 varchar column of size 250 (regardless of parsing). You ought to read the mysql manual on that. http://www.mysql.com/doc/en/MyISAM_table_formats.html Where's the pain? The trade off between char and varchar is speed vs table size. Are just trying to be as fast as possible? If the db is small, I wouldn't worry about it and do what ever way you want (i.e. what's a microsecond or two?) You could try both ways and profile it several thousand times to see if it really matters. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql/php database performance question
keeping those 5 columns with a lot of empty cells in the last columns is better as it is means exactly for that. Just don't make it a not null field. -- Maxim Maletsky [EMAIL PROTECTED] Khalid El-Kary [EMAIL PROTECTED] wrote... : the thing that may make difference in performance (as i think) is whether you make it a fixed -CHAR- or a variable -VARCHAR- it's preferable that you make separate char columns, so that PHP will not have to explode every record! Regards, Khalid Al-Kary, Hi, I got a question about using Mysql databases. I load textdata in VARCHAR colums up to size 50. I have about 5 of those columns. The last columns often contain empty cells. (data are wordmeanings, many words have only a 1 or 2meanings) What would be faster/better: - putting everything in a big varchar column (size 5x50) and PHP parsing them by comma after fetching or - keeping those 5 columns with a lot of empty cells in the last columns? Thanks, Simon -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ The new MSN 8 is here: Try it free* for 2 months http://join.msn.com/?page=dept/dialup -- 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/php database performance question
char of greater size than 3 is converted to varchar anyways Where's the pain? The trade off between char and varchar is speed vs table size. Are just trying to be as fast as possible? If the db is small, I wouldn't worry about it and do what ever way you want (i.e. what's a microsecond or two?) You could try both ways and profile it several thousand times to see if it really matters. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql/php database performance question
-Original Message- From: Marek Kilimajer [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 11:04 AM To: Matt Schroebel Cc: Simon Dedeyne; [EMAIL PROTECTED] Subject: Re: [PHP] Mysql/php database performance question char of greater size than 3 is converted to varchar anyways Are you sure? I've been reading up on this stuff over the last few days, and my understanding is that char is stored fixed width with trailing spaces padding the string to the length specified in the schema, whereas varchar is stored is strlen(rtrim(column))+1. So a column char(45) will always take 45 bytes of space, while varchar(45) will vary from 1 to 46 bytes of space. The first way makes locating a row in the db fast (as long as all columns are fixed width [No blob, text, or varchar columns]) since it's simple math, whereas the latter way saves space but makes MYSQLs finding a row a little harder (since the offset varies) and thus a bit slower. I have always been using varchar and have been considering changing to char. http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html #CHAR -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql/php database performance question
Sure, just tried it (32-bit platform, might be 7 for 64-bits). I have a feeling it is somewhere in the manual. Matt Schroebel wrote: -Original Message- From: Marek Kilimajer [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 11:04 AM To: Matt Schroebel Cc: Simon Dedeyne; [EMAIL PROTECTED] Subject: Re: [PHP] Mysql/php database performance question char of greater size than 3 is converted to varchar anyways Are you sure? I've been reading up on this stuff over the last few days, and my understanding is that char is stored fixed width with trailing spaces padding the string to the length specified in the schema, whereas varchar is stored is strlen(rtrim(column))+1. So a column char(45) will always take 45 bytes of space, while varchar(45) will vary from 1 to 46 bytes of space. The first way makes locating a row in the db fast (as long as all columns are fixed width [No blob, text, or varchar columns]) since it's simple math, whereas the latter way saves space but makes MYSQLs finding a row a little harder (since the offset varies) and thus a bit slower. I have always been using varchar and have been considering changing to char. http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html #CHAR -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql/php database performance question
-Original Message- From: Marek Kilimajer [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 11:45 AM To: Matt Schroebel Cc: Simon Dedeyne; [EMAIL PROTECTED] Subject: Re: [PHP] Mysql/php database performance question Sure, just tried it (32-bit platform, might be 7 for 64-bits). I have a feeling it is somewhere in the manual. How'd you try it? I created a 1 column 42 char record in phpMyAdmin. Everytime I add a row, regardless of size the dataspace increases by 42. With a second table, with 1 column varchar(42), each 4-5 char insert resulted in 20 bytes of space (must be some minimum overhead), and a full 42 resulted in 44 bytes of dataspace used. I'm curious here, as it seems the trade off is speed of access with char [and the overhead of removing trailing spaces on each retrieval] vs storage size in varchar [and it's improved strip right spaces on storage only happening once]. That's what the man page I pointed to last time said. There are some examples of truncating data to 4 bytes on that page but no mention of storing char as varchar. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql/php database performance question
seems to be a little bit more complicated: CREATE TABLE `aa` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `aaa` CHAR( 50 ) NOT NULL , `bbb` CHAR( 50 ) NOT NULL ); both aaa and bbb are char now CREATE TABLE `aaa` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `aaa` CHAR( 50 ) NOT NULL , `bbb` VARCHAR( 255 ) NOT NULL ); aaa will be varchar anyway CREATE TABLE `aaa` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `aaa` CHAR( 50 ) NOT NULL , ); aaa is char ALTER TABLE `aaa` ADD `bbb` VARCHAR( 250 ) NOT NULL ; aaa in now VARCHAR Seems like one cannot mix char and varchar columns in one table Matt Schroebel wrote: -Original Message- From: Marek Kilimajer [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 11:45 AM To: Matt Schroebel Cc: Simon Dedeyne; [EMAIL PROTECTED] Subject: Re: [PHP] Mysql/php database performance question Sure, just tried it (32-bit platform, might be 7 for 64-bits). I have a feeling it is somewhere in the manual. How'd you try it? I created a 1 column 42 char record in phpMyAdmin. Everytime I add a row, regardless of size the dataspace increases by 42. With a second table, with 1 column varchar(42), each 4-5 char insert resulted in 20 bytes of space (must be some minimum overhead), and a full 42 resulted in 44 bytes of dataspace used. I'm curious here, as it seems the trade off is speed of access with char [and the overhead of removing trailing spaces on each retrieval] vs storage size in varchar [and it's improved strip right spaces on storage only happening once]. That's what the man page I pointed to last time said. There are some examples of truncating data to 4 bytes on that page but no mention of storing char as varchar. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql/php database performance question
Oh, sorry, i should have remarked that all of the columns should be fixed width (CHAR) for this optimization to take effect, since if even one of the columns is variable width (VARCHAR) the whole row will be variable width, and it will be no use to change to CHAR. Regards, Khalid seems to be a little bit more complicated: CREATE TABLE `aa` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `aaa` CHAR( 50 ) NOT NULL , `bbb` CHAR( 50 ) NOT NULL ); both aaa and bbb are char now CREATE TABLE `aaa` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `aaa` CHAR( 50 ) NOT NULL , `bbb` VARCHAR( 255 ) NOT NULL ); aaa will be varchar anyway CREATE TABLE `aaa` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `aaa` CHAR( 50 ) NOT NULL , ); aaa is char ALTER TABLE `aaa` ADD `bbb` VARCHAR( 250 ) NOT NULL ; aaa in now VARCHAR Seems like one cannot mix char and varchar columns in one table Matt Schroebel wrote: -Original Message- From: Marek Kilimajer [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 11:45 AM To: Matt Schroebel Cc: Simon Dedeyne; [EMAIL PROTECTED] Subject: Re: [PHP] Mysql/php database performance question Sure, just tried it (32-bit platform, might be 7 for 64-bits). I have a feeling it is somewhere in the manual. How'd you try it? I created a 1 column 42 char record in phpMyAdmin. Everytime I add a row, regardless of size the dataspace increases by 42. With a second table, with 1 column varchar(42), each 4-5 char insert resulted in 20 bytes of space (must be some minimum overhead), and a full 42 resulted in 44 bytes of dataspace used. I'm curious here, as it seems the trade off is speed of access with char [and the overhead of removing trailing spaces on each retrieval] vs storage size in varchar [and it's improved strip right spaces on storage only happening once]. That's what the man page I pointed to last time said. There are some examples of truncating data to 4 bytes on that page but no mention of storing char as varchar. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Mysql/php database performance question
I'm coming in a little late on this thread, but I would suggest structuring you data differently. I would have two tables: words and meanings. Then you won't have any null fields and you won't limit yourself to just 5 definitions. It will also be a heck of a lot easier to setup a full text index on the meaning since you only need to index one field instead of 5. On Friday, January 10, 2003, at 10:49 AM, Maxim Maletsky wrote: What would be faster/better: - putting everything in a big varchar column (size 5x50) and PHP parsing them by comma after fetching or - keeping those 5 columns with a lot of empty cells in the last columns? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql, php, checkbox
Indeed, now it works, I put the form tag by mistake...
Thanks,
Adrian
- Original Message -
From: rija [EMAIL PROTECTED]
To: Adrian Partenie [EMAIL PROTECTED]
Sent: Tuesday, December 03, 2002 11:30 PM
Subject: Re: [PHP] mysql, php, checkbox
There are form/form tags around input type=checkbox ... What are
they
supposed to do?
I think your problem lies there, because ids[] belong to this new form not
to the first one and then ids[] cannot be set.
Secondly, method='post' inside input does nothing.
Hope that helps.
- Original Message -
From: Adrian Partenie [EMAIL PROTECTED]
To: php [EMAIL PROTECTED]
Sent: Wednesday, December 04, 2002 4:50 AM
Subject: Re: [PHP] mysql, php, checkbox
It works, but just for the first row selected from the table. I think
that
the problem is that the checkboxes are declared inside a while loop. If
i
declare manually all checkboxes it works. Any ideas ? Or maybe I'm doing
something wrong?
echo form method=\post\ action=\selectare.php?ids[]\;
echo input type=\Submit\ value=\Trimite\;
/* Connecting, selecting database */
$link = mysql_connect(localhost, root, adrian)
or die(Could not connect);
print Connected successfully;
mysql_select_db(menagerie) or die(Could not select database);
/* Performing SQL query */
$query = SELECT * FROM reclamatie;
$result = mysql_query($query) or die(Query failed);
/* Printing results in HTML */
echo table border=1;
echo
trtd/tdtdID/tdtdSubject/tdtdOpen/tdtdClose/td/tr;
while($row = MySQL_fetch_array($result)) {
echo trtdforminput type=\checkbox\ method=\post\
name=\ids[]\ value=\{$row['id']}\/form/td;
echo tda href=\lowerframe.php?id={$row['id']}\
target=\lowerframe\{$row['id']}/a/td;
echo td{$row['subject']}/td;
echo td{$row['open']}/td;
echo td{$row['close']}/td/tr;
}
echo /table;
/* Free resultset */
mysql_free_result($result);
/* Closing connection */
mysql_close($link);
echo /form;
?
###
//selectare.php (just displays the id's of selected checkboxes)
//$useri=$_POST['useri'];
$ids=$_POST['ids'];
reset($ids);
while (list ($key, $value) = each ($ids)) {
echo $valuebr /\n;
}
?
#
As I said, when I select the first checkbox, I get the id, but when I
select
any other checkbox, I get the errors
PHP Notice: Undefined index: ids in selectare.php
PHP Warning: Variable passed to each() is not an array or object in
selectare.php
- Original Message -
From: John W. Holmes [EMAIL PROTECTED]
To: 'Adrian Partenie' [EMAIL PROTECTED]; 'php'
[EMAIL PROTECTED]
Sent: Thursday, November 28, 2002 5:54 PM
Subject: RE: [PHP] mysql, php, checkbox
I'm displaying the content of a mysql table with autoincrement
index.
I
want to be able to select the each row from the table using the
check
boxes. In order to do that, i want to assign to each checkbox the
name=index of selected row.
I assign to the checkboxes the value of selected id, but I can't
retreiveit later for further processing. My code looks like this
$query = SELECT * FROM reclamatie;
$result = mysql_query($query) or die(Query failed);
/* Printing results in HTML */
echo table border=1;
echo
trtd/tdtdID/tdtdSubject/tdtdOpen/tdtdClose/td/tr
;
while($row = MySQL_fetch_array($result)) {
echo trtdforminput type=\checkbox\
method=\post\
name=\{$row['id']}\/form/td; // ??
It looks like your naming it as a number, which won't work for PHP.
You
want to name all of your checkboxes the same, with a [] on the name to
make the results an array in PHP.
... name=id[] value={$row['id']}
Then, you'll have $_POST['id'][x] as an array in PHP. Only the
checkboxes that were selected will be in the array, from zero to
however
many were checked. The value of $_POST['id'][x] will be whatever was
in
the value=... part of the HTML checkbox...
---John Holmes...
--
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, php, checkbox
It works, but just for the first row selected from the table. I think that
the problem is that the checkboxes are declared inside a while loop. If i
declare manually all checkboxes it works. Any ideas ? Or maybe I'm doing
something wrong?
echo form method=\post\ action=\selectare.php?ids[]\;
echo input type=\Submit\ value=\Trimite\;
/* Connecting, selecting database */
$link = mysql_connect(localhost, root, adrian)
or die(Could not connect);
print Connected successfully;
mysql_select_db(menagerie) or die(Could not select database);
/* Performing SQL query */
$query = SELECT * FROM reclamatie;
$result = mysql_query($query) or die(Query failed);
/* Printing results in HTML */
echo table border=1;
echo
trtd/tdtdID/tdtdSubject/tdtdOpen/tdtdClose/td/tr;
while($row = MySQL_fetch_array($result)) {
echo trtdforminput type=\checkbox\ method=\post\
name=\ids[]\ value=\{$row['id']}\/form/td;
echo tda href=\lowerframe.php?id={$row['id']}\
target=\lowerframe\{$row['id']}/a/td;
echo td{$row['subject']}/td;
echo td{$row['open']}/td;
echo td{$row['close']}/td/tr;
}
echo /table;
/* Free resultset */
mysql_free_result($result);
/* Closing connection */
mysql_close($link);
echo /form;
?
###
//selectare.php (just displays the id's of selected checkboxes)
//$useri=$_POST['useri'];
$ids=$_POST['ids'];
reset($ids);
while (list ($key, $value) = each ($ids)) {
echo $valuebr /\n;
}
?
#
As I said, when I select the first checkbox, I get the id, but when I select
any other checkbox, I get the errors
PHP Notice: Undefined index: ids in selectare.php
PHP Warning: Variable passed to each() is not an array or object in
selectare.php
- Original Message -
From: John W. Holmes [EMAIL PROTECTED]
To: 'Adrian Partenie' [EMAIL PROTECTED]; 'php'
[EMAIL PROTECTED]
Sent: Thursday, November 28, 2002 5:54 PM
Subject: RE: [PHP] mysql, php, checkbox
I'm displaying the content of a mysql table with autoincrement index.
I
want to be able to select the each row from the table using the check
boxes. In order to do that, i want to assign to each checkbox the
name=index of selected row.
I assign to the checkboxes the value of selected id, but I can't
retreiveit later for further processing. My code looks like this
$query = SELECT * FROM reclamatie;
$result = mysql_query($query) or die(Query failed);
/* Printing results in HTML */
echo table border=1;
echo
trtd/tdtdID/tdtdSubject/tdtdOpen/tdtdClose/td/tr
;
while($row = MySQL_fetch_array($result)) {
echo trtdforminput type=\checkbox\ method=\post\
name=\{$row['id']}\/form/td; // ??
It looks like your naming it as a number, which won't work for PHP. You
want to name all of your checkboxes the same, with a [] on the name to
make the results an array in PHP.
... name=id[] value={$row['id']}
Then, you'll have $_POST['id'][x] as an array in PHP. Only the
checkboxes that were selected will be in the array, from zero to however
many were checked. The value of $_POST['id'][x] will be whatever was in
the value=... part of the HTML checkbox...
---John Holmes...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql, php, checkbox
Hello,
I'm displaying the content of a mysql table with autoincrement index. I want to be
able to select the each row from the table using the check boxes. In order to do that,
i want to assign to each checkbox the name=index of selected row.
I assign to the checkboxes the value of selected id, but I can't retreiveit later for
further processing. My code looks like this
$query = SELECT * FROM reclamatie;
$result = mysql_query($query) or die(Query failed);
/* Printing results in HTML */
echo table border=1;
echo trtd/tdtdID/tdtdSubject/tdtdOpen/tdtdClose/td/tr;
while($row = MySQL_fetch_array($result)) {
echo trtdforminput type=\checkbox\ method=\post\
name=\{$row['id']}\/form/td; // ??
echo tda href=\lowerframe.php?id={$row['id']}\
target=\lowerframe\{$row['id']}/a/td; //for other purposes, it works fine
echo td{$row['subject']}/td;
echo td{$row['open']}/td;
echo td{$row['close']}/td/tr;
}
echo /table;
Any sugestions if i want to use forms, something like
form method=post action=selectare.php?id=$row['id']
input type=Submit value=Trimite
/form
Thanks, Adrian
RE: [PHP] mysql, php, checkbox
I'm displaying the content of a mysql table with autoincrement index.
I
want to be able to select the each row from the table using the check
boxes. In order to do that, i want to assign to each checkbox the
name=index of selected row.
I assign to the checkboxes the value of selected id, but I can't
retreiveit later for further processing. My code looks like this
$query = SELECT * FROM reclamatie;
$result = mysql_query($query) or die(Query failed);
/* Printing results in HTML */
echo table border=1;
echo
trtd/tdtdID/tdtdSubject/tdtdOpen/tdtdClose/td/tr
;
while($row = MySQL_fetch_array($result)) {
echo trtdforminput type=\checkbox\ method=\post\
name=\{$row['id']}\/form/td; // ??
It looks like your naming it as a number, which won't work for PHP. You
want to name all of your checkboxes the same, with a [] on the name to
make the results an array in PHP.
... name=id[] value={$row['id']}
Then, you'll have $_POST['id'][x] as an array in PHP. Only the
checkboxes that were selected will be in the array, from zero to however
many were checked. The value of $_POST['id'][x] will be whatever was in
the value=... part of the HTML checkbox...
---John Holmes...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySql-PHP-Excel=french weird chars
Hi all I'm using a MySql database to save French data. In addition, I have a PHP page which is using header(Content-Type: application/vnd.ms-excel; name='excel'); header(Content-Disposition: attachment; filename=filename.xls); to save all the data from the database to the excel file. The problem is that when I open the excel file I get this: é instead of this: é and the same thing for all the French accented chars. I've tried to save the data as acsv file too but I get the same problem. Does anyone have a solution? Many Thanks Mario _ Marios Adamantopoulos Senior Developer Tonic +44 (0)20 7691 2227 +44 (0)7904 221663 www.tonic.co.uk Recent projects www.polydor.co.uk www.adcecreative.org www.sony-europe.com/pocketlife Opinions, conclusions and other information in this message that do not relate to the official business of Tonic Design Limited shall be understood as neither given nor endorsed by them.
[PHP] mysql/php large integer query oddity
I have a query oddity that looks like an integer overflow, but it shouldn't be.
Excerpt from my program:
snip
#DEBUG
echo \nbr$Querybr\n;
#DEBUG
$hResult = _do_query(__LINE__, $Query); # _do_query() simply
executes mysql_query, and does nice error formatting if necessary
$First = true;
while ($Row = @mysql_fetch_array($hResult)) {
if ($First) {
$First = false;
show_table_open($Myself);
}
echo
'tr bgcolor=', $TableColor['row1'], '',
'td', $Row['source_code'], '/td',
'td', $Row['chromo_code'], '/td',
'td', $Row['type'], '/td',
'td align=right', number_format($Row['min_exon_length']), '/td',
'td align=right', number_format($Row['max_exon_length']), '/td',
# 'td', $Row['min_exon_length'], '/td',
# 'td', $Row['max_exon_length'], '/td',
/tr\n;
}
snip
This produces the output
select sm.source_code,chromo_code,type,min(end_coord-start_coord+1)
as min_exon_length,max(end_coord-start_coord+1) as max_exon_length
from source_master sm left join chromosome_master chm using
(source_code) left join contig_master cm on
(cm.chromosome_id=chm._id) left join contig_position cp on
(cp.contig_id=cm._id) group by chromosome_id,type order by
sm.source_code,chromo_code,type
snip
Source Chromosome Type Min_exon_length Max_exon_length
snip
tigr 1 pseudogene 59 14,623
tigr 1 TIGR: unspecified type -20,699,646 29,780,051
tigr 2 pseudogene 248,789
tigr 2 TIGR: unspecified type -18,834,528 16,190,138
tigr 3 pseudogene 39,302
tigr 3 TIGR: unspecified type -23,058,902 14,292,141
tigr 4 pseudogene 248,949
tigr 4 TIGR: unspecified type -9,199,9049,000,303
tigr 5 pseudogene 12 14,463
tigr 5 TIGR: unspecified type -14,334,542 26,344,478
HOWEVER, if I cut paste the echoed query to the mysql commandline, I get:
+-+-++-+-+
| source_code | chromo_code | type |
min_exon_length | max_exon_length |
+-+-++-+-+
snip
| tigr| 1 | pseudogene |
59 | 14623 |
| tigr| 1 | TIGR: unspecified type |
2 |29780051 |
| tigr| 2 | pseudogene |
24 |8789 |
| tigr| 2 | TIGR: unspecified type |
2 |18834530 |
| tigr| 3 | pseudogene |
3 |9302 |
| tigr| 3 | TIGR: unspecified type |
1 |23058904 |
| tigr| 4 | pseudogene |
24 |8949 |
| tigr| 4 | TIGR: unspecified type |
2 | 9199906 |
| tigr| 5 | pseudogene |
12 | 14463 |
| tigr| 5 | TIGR: unspecified type |
2 |26344478 |
+-+-++-+-+
75 rows in set (9.15 sec)
Notice that I get bizarre results on min_exon_length for the
'unspecified type' lines; in addition, the max_exon_length is off 3
of the 5 times. All of the other 75 rows are identical. Is there some
setting in my.cnf or php.ini that I need to check - perhaps
mysql_always_return_correct_results? I tried echoing the results out
directly instead of using number_format(), but there was no change in
the numbers. I've never seen anything like this...perhaps I need more
coffee?
Versions:
Apache 1.3.26/php 4.1.2, using mysql client API 3.23.47
MySQL 3.23.47-log
Platform: SunOS 5.8 sun4u sparc SUNW,Sun-Fire-280R
Thanks in advance,
steve
--
++
| Steve Edberg [EMAIL PROTECTED] |
| University of California, Davis (530)754-9127 |
| Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002 |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens... |
++
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
