RE: [PHP-DB] sql problem
http://www.php.net/manual/en/function.number-format.php bastien Date: Sun, 16 Dec 2007 17:17:41 +0600 From: [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] sql problem my problem in the following code INSERT INTO `test` ( `debit` ) VALUES ( '2' ) when i search it shows like this: SELECT debit FROM `test` output is :2. but i have to show output :20,000.00 like input 2 output 20,000.00 input 3000 output 3,000.00 input 10 output 1,0.00 _ Read what Santa`s been up to! For all the latest, visit asksantaclaus.spaces.live.com! http://asksantaclaus.spaces.live.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] sql, problem with join and presentation
i think the newer versions of MYSQL allow for subselects and I think that is what you want. see www.mysql.net -Original Message- From: mayo [mailto:[EMAIL PROTECTED] Sent: Sunday, February 15, 2004 11:56 PM To: php-db Subject: [PHP-DB] sql, problem with join and presentation Currently I display a list of classes. Simplified SQL and display below: SELECT * FROM classes WHERE classCategory='$Category' AND classDeleted=0 ORDER BY $order $reorder The presentation is: +--+--++ | CLASS TITLE | LOCATION | CLASS CODE | +--+--++ | CLASS DESCRIPTION br/br/ | | CLASS INSTRUCTOR br/br/ | | CLASS TIME | +--+ Now, things are getting a little more complicated. Each class is going to have sections. So the display will be: CLASS TITLE CLASS DESCRIPTION CLASS CODE : CLASS SECTION .. LOCATION .. CLASS TIME .. INSTRUCTOR example (simplified) +-+ | INTRO TO AAA| +-+ | This is a really interesting | | | +-+-+++ |HT-111:A | NYC | 12:00-4:00 | Albert Alkin | |HT-111:B | JC | 2:00-6:00 | Bob Bailey | |HT-111:C | BX | 4:00-8:00 | Chris Cawley | +-+-+++ I'm having a really hard time coming up with the sql for this. I want to (pseudo) select * from classes and classSections where classDeleted=0 and group by classCode tables below CLASSES classID classDescription classTexts classCost classDeleted CLASSCODES classCodeID classID classCodeSection classDate classTime classLocation classInstructor I'm going nuts trying to get this. I must be missing something simple. (using mysql) thx for any clues Gil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Disclaimer This e-mail transmission contains confidential information, which is the property of the sender. The information in this e-mail or attachments thereto is intended for the attention and use only of the addressee. Should you have received this e-mail in error, please delete and destroy it and any attachments thereto immediately. Under no circumstances will the Cape Technikon or the sender of this e-mail be liable to any party for any direct, indirect, special or other consequential damages for any use of this e-mail. For the detailed e-mail disclaimer please refer to http://www.ctech.ac.za/polic or call +27 (0)21 460 3911 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql, problem with join and presentation
There are two ways to do what you ask:
1 - run your first query (just the classes no sections) and before the loop
to display the results open a new - different connection to the DB then
in the loop where you are displaying the results after displaying each class
run a query to find all of it's sections and display them. Possible
weakness is many connections to the DB (not a big deal but may not scale
well) and if there is a class that has no section this quarter then you
would only find that out after you displayed the class (could be fixed in
your first query or by checking for sections before you displayed the class)
2 - Select everything (classes and sections) in one query
SELECT *
FROM classes, CLASSCODES
WHERE classCategory='$Category'
AND classDeleted=0
AND CLASSCODES.classID = CLASSES classID
ORDER BY $order $reorder -- not sure what you are doing here but you
will need to add classID at the end of this list.
Now you will get back these columns:
classID classDescription classTexts classCost classDeleted classCodeID
classID classCodeSection classDate classTime classLocation classInstructor
And the columns from the CLASSES table will be duplicated for each section
(this is why you must sort by classID to keep them all together).
So before the loop to display the results you set $thisClassID = 0;
And first thing in the loop you check:
if ($thisClassID != result[classID])
{
/*This is a new class, display it's info*/
echo result[classDescription]
/* don't forget to reset this */
$thisClassID = result[classID];
}
/* now display the section info... */
Good Luck,
Frank
On 2/17/04 9:49 AM, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
From: mayo [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 15, 2004 11:56 PM
To: php-db
Subject: [PHP-DB] sql, problem with join and presentation
Currently I display a list of classes.
Simplified SQL and display below:
SELECT *
FROM classes
WHERE
classCategory='$Category' AND
classDeleted=0
ORDER BY $order $reorder
The presentation is:
+--+--++
| CLASS TITLE | LOCATION | CLASS CODE |
+--+--++
| CLASS DESCRIPTION br/br/ |
| CLASS INSTRUCTOR br/br/ |
| CLASS TIME |
+--+
Now, things are getting a little more complicated. Each class is going to
have sections. So the display will be:
CLASS TITLE
CLASS DESCRIPTION
CLASS CODE : CLASS SECTION .. LOCATION .. CLASS TIME .. INSTRUCTOR
example (simplified)
+-+
| INTRO TO AAA|
+-+
| This is a really interesting |
| |
+-+-+++
|HT-111:A | NYC | 12:00-4:00 | Albert Alkin |
|HT-111:B | JC | 2:00-6:00 | Bob Bailey |
|HT-111:C | BX | 4:00-8:00 | Chris Cawley |
+-+-+++
I'm having a really hard time coming up with the sql for this.
I want to (pseudo)
select *
from classes and classSections
where classDeleted=0
and group by classCode
tables below
CLASSES
classID
classDescription
classTexts
classCost
classDeleted
CLASSCODES
classCodeID
classID
classCodeSection
classDate
classTime
classLocation
classInstructor
I'm going nuts trying to get this. I must be missing something simple.
(using mysql)
thx for any clues
Gil
--
Frank Flynn
Poet, Artist Mystic
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL problem is killing this newbie
The following will list movies seen by each member: SELECT membername,movietitle FROM member,movie WHERE member.memberid=movie.memberid This one lists all members who have seen these 3 movies. SELECT membername FROM member,movie WHERE member.memberid=movie.memberid movietitle=Star Wars movietitle=Star Trek movietitle=Planet of the Apes In practice, you would construct the movies' titles' AND clause in you PHP script -Original Message- From: Samios [mailto:[EMAIL PROTECTED]] Sent: Monday, December 10, 2001 9:58 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] SQL problem is killing this newbie I am having a problem designing a query on a mysql 3.23 database. I have 2 tables - member and movie. The member table stores the people details, the movie table records the movies they have seen (each record is one movie). MEMBER memberid int not null auto_increment primary key, membername varchar(50) not null, etc... MOVIE movieid int not null auto_increment primary key, movietitle varchar(50) not null, memberid int, index (memberid) etc... Obviously member to movie is a one-to-many relationship. I want to create a search page which will allow me to search for members who have watched a specified set of movies. i.e. I want to query the database for members who have seen Star Trek AND Star Wars AND Planet of the APES. I can create a query which searches for Star Trek OR Star Wars OR Planet of the Apes. Unfortunately the AND condition is causing me problems. I'm also hoping to use the LIKE operator in this query. e.g. where movietitle like %star%. This will give me added flexiblity down the track. Any help would be greatly appreciated. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] SQL problem
That is the strange thing, there are no error messages. I simply don't get short_desc with list($image,$desc)=mysql_fetch_row($res1); $desc is empty. I will try your query. -Original Message- From: Dobromir Velev [mailto:[EMAIL PROTECTED]] Sent: petak 31. avgust 2001. 10:04 To: Robert Vukovic; [EMAIL PROTECTED] Subject: Re: [PHP-DB] SQL problem Hi, Are there any error messages? Just a guess - may be this will work Select $tbl_virtual.thumb1,$tbl_descriptions.short_desc FROM $tbl_virtual left join $tbl_descriptions on $tbl_virtual.id_property=$tbl_descriptions.id_property WHERE $tbl_virtual.id_property=$link Dobromir Velev -Original Message- From: Robert Vukovic [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Friday, August 31, 2001 7:34 AM Subject: RE: [PHP-DB] SQL problem Table was created with this CREATE TABLE `descriptions` ( `id_property` bigint(20) NOT NULL default '0', `short_desc` varchar(255) default NULL, `long_desc` text, KEY `id_property`(`id_property`), PRIMARY KEY (`id_property`), UNIQUE KEY `id_property_2`(`id_property`) ) TYPE=MyISAM COMMENT=''; and this is query: $res1=mysql_query(SELECT $tbl_virtual.thumb1,$tbl_descriptions.short_desc FROM $tbl_virtual,$tbl_descriptions WHERE $tbl_virtual.id_property=$link and $tbl_descriptions.id_property=$link) virtual is another table with picture names. This query work at my home but not on real site. What is a problem. ? I forgot to say that PHP is 4.04 and I am using MySQL -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] SQL problem
Hi, Are there any error messages? Just a guess - may be this will work Select $tbl_virtual.thumb1,$tbl_descriptions.short_desc FROM $tbl_virtual left join $tbl_descriptions on $tbl_virtual.id_property=$tbl_descriptions.id_property WHERE $tbl_virtual.id_property=$link Dobromir Velev -Original Message- From: Robert Vukovic [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Friday, August 31, 2001 7:34 AM Subject: RE: [PHP-DB] SQL problem Table was created with this CREATE TABLE `descriptions` ( `id_property` bigint(20) NOT NULL default '0', `short_desc` varchar(255) default NULL, `long_desc` text, KEY `id_property`(`id_property`), PRIMARY KEY (`id_property`), UNIQUE KEY `id_property_2`(`id_property`) ) TYPE=MyISAM COMMENT=''; and this is query: $res1=mysql_query(SELECT $tbl_virtual.thumb1,$tbl_descriptions.short_desc FROM $tbl_virtual,$tbl_descriptions WHERE $tbl_virtual.id_property=$link and $tbl_descriptions.id_property=$link) virtual is another table with picture names. This query work at my home but not on real site. What is a problem. ? I forgot to say that PHP is 4.04 and I am using MySQL -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] SQL problem
Table was created with this CREATE TABLE `descriptions` ( `id_property` bigint(20) NOT NULL default '0', `short_desc` varchar(255) default NULL, `long_desc` text, KEY `id_property`(`id_property`), PRIMARY KEY (`id_property`), UNIQUE KEY `id_property_2`(`id_property`) ) TYPE=MyISAM COMMENT=''; and this is query: $res1=mysql_query(SELECT $tbl_virtual.thumb1,$tbl_descriptions.short_desc FROM $tbl_virtual,$tbl_descriptions WHERE $tbl_virtual.id_property=$link and $tbl_descriptions.id_property=$link) virtual is another table with picture names. This query work at my home but not on real site. What is a problem. ? I forgot to say that PHP is 4.04 and I am using MySQL -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] sql problem
Fai [EMAIL PROTECTED] wrote in message 9h90l2$1op$[EMAIL PROTECTED]">news:9h90l2$1op$[EMAIL PROTECTED]... SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summmary=@A WHERE type=1; What does @A: mean? Thank you very much! They're writing the sum to an SQL variable. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] sql problem
@a is here an internal 'session' variable used in sql databases (atleast mysql). simple usage : select @a:=1; - 1 select @b:=10; - 10 select @a + @b; - 11 These variables are used to hold temporary numbers and characters, so the interaction between the script language and the database can be held to a minimum. Mats Remman PHP Developer, MySQL DBA Coretrek, Norway +47 51978591 / +47 91623566 -Original Message- From: Fai [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 26, 2001 5:55 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] sql problem SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summmary=@A WHERE type=1; What does @A: mean? Thank you very much! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] sql problem
A quick glance suggests to me that it is a nonstandard proprietary extension to SQL that provides a way to set a kind of a variable and use it in a subsequent nonstandard proprietary SQL-looking statement that is awfully procedural. You'll probably want to either consult the documentation for the RDBMS you use or ask your question on a mailing list tailored to users and/or developers of the RDBMS you're using for a definitive answer. I can verify for you that it's not standard SQL, though. And it looks to me like they've gone and made what appears to be an extension that extends SQL so it has some procedural capabilities. There are problems on many levels with using SQL as a procedural language. The most obvious problem with using nonstandard proprietary procedural extensions to SQL is that (for example) you couldn't change backend database without recoding all your nonstandard SQL-looking statements. But I suppose they can be helpful because you couldn't possibly do the same thing with PHP (or perl, or a C program, or...) and standard SQL. Oh, wait. You can do procedural things with de-facto standard and standard procedural languages? Well, I'm sure there's a reason to have nonstandard proprietary extensions that add procedural capabilities to SQL or they wouldn't have them. (Note the clever way I mentioned PHP, since this is a PHP mailing list and both the question and the answer have nothing to do with PHP. It still doesn't bring it on topic, but at least I tried.) Doug At 11:54 AM 6/26/01 +0800, Fai wrote: SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summmary=@A WHERE type=1; What does @A: mean? Thank you very much! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
