Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:40, Daniel P. Brown wrote: > For your convenience, both have been CC'd on this email. Actually, PHP-DB (php...@lists.php.net) was accidentally BCC'd. -- Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:35, Don Wieland wrote: > Hi all, > > Is there a list/form to get some help on compiling mySQL queries? I am > executing them via PHP, but do not want to ask for help here if it is no the > appropriate forum. Thanks ;-) Yes. For MySQL queries, write to the MySQL General list at my...@lists.mysql.com. For PHP-specific database questions (for any database backend, not strictly MySQL), such as problems in connecting to the database, questions on support for database platform/version, or even query processing, you should use php...@lists.php.net. For your convenience, both have been CC'd on this email. -- Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mySQL query assistance...
Hi all, Is there a list/form to get some help on compiling mySQL queries? I am executing them via PHP, but do not want to ask for help here if it is no the appropriate forum. Thanks ;-) Don -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
On Sun, 21 Nov 2010 11:19:04 -0700 "Ben Miller" wrote: > > To help clarify - the 3 tables look something like the following (tableName > => column,column,column...): > > Products => product_id,product_name,product_description... (key = > product_id) > Criteria => criteria_id,criteria_title,criteria_text,... (key = > criteria_id) > Criteria_values => product_id,criteria_id,criteria_value,... (key = > product_id & criteria_id) > > The user selects up to X product_id's to compare, stored in > $selected_products. > > I then need to get each criteria_title and criteria_text from > table(criteria) where there is a matching criteria_id in > table(criteria_values) for each/all $selected_products, also returning the > criteria_value for each $selected_products, ultimately ending up with an > array or object that looks something like: > > (Assuming the user selected Product A (product_id=1), Product B > (product_id=2) and Product C (product_id=3) > > criteria => Array ( > [$criteria_id] => Array ( > [title] => query_row[criteria_title] > [text] => query_row[criteria_text] > [values] => Array ( > [1] => Product A's value for this criteria > [2] => Product B's value for this criteria > [3] => Product C's value for this criteria > ) > ) > [$criteria_id] => Array ( > . > ) > ) > > Again, displaying only/all criteria where there is a matching value for > each/all $selected_products > > Thanks again, > Ben > > You should probably select all relevant rows for each product, without checking that a given criteria has a matching value for each product. Use php to filter out the criteria which do not apply to all products. -- Simcha Younger -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help
SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT
JOIN criteria c ON cv.key=c.key WHERE c.value IS NOT NULL
Hard to answer without more detail, but I am guessing the answer will be
something like the above. Your question makes it hard to understand whether
c or cv is joined to p. So swap em around if I misunderstood.
iPhone 4. It rocks!
On Nov 21, 2010, at 1:37 AM, Simcha Younger wrote:
> On Sat, 20 Nov 2010 13:54:29 -0700
> "Ben Miller" wrote:
>
>> Hi,
>>
>> I'm building a website for a client in which I need to compare their
>> products, side-by-side, but only include criteria for which all
>> selected products have a value for that criteria.
>>
>> In my database (MySQL), I have a tables named "products","criteria"
>> and "criteria_values"
>>
>> If I have something like
>>
>> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) //
>> All products selected for comparison by the user
>>
>> I need to get only rows from "criteria" where there is a row in
>> "criteria_values" matching "criteria.criteria_id" for each
>> $selected_product
>> - in other words, if any of the $selected_product does not have a row
>> in "criteria_values" that matches "criteria.criteria_id", that
>> criteria would not be returned. I hope that makes sense.
>
> It would be a lot easier to think about this if you could provide the
table structure or create table statements.
>
> If I understood correctly, you have products which reference a criteria ID
which has no matching value. If this is the problem you have a to first take
care of the integrity of your data, as this should never happen.
>
To help clarify - the 3 tables look something like the following (tableName
=> column,column,column...):
Products => product_id,product_name,product_description... (key =
product_id)
Criteria => criteria_id,criteria_title,criteria_text,... (key =
criteria_id)
Criteria_values => product_id,criteria_id,criteria_value,... (key =
product_id & criteria_id)
The user selects up to X product_id's to compare, stored in
$selected_products.
I then need to get each criteria_title and criteria_text from
table(criteria) where there is a matching criteria_id in
table(criteria_values) for each/all $selected_products, also returning the
criteria_value for each $selected_products, ultimately ending up with an
array or object that looks something like:
(Assuming the user selected Product A (product_id=1), Product B
(product_id=2) and Product C (product_id=3)
criteria => Array (
[$criteria_id] => Array (
[title] => query_row[criteria_title]
[text] => query_row[criteria_text]
[values] => Array (
[1] => Product A's value for this criteria
[2] => Product B's value for this criteria
[3] => Product C's value for this criteria
)
)
[$criteria_id] => Array (
.
)
)
Again, displaying only/all criteria where there is a matching value for
each/all $selected_products
Thanks again,
Ben
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
SELECT * FROM products p LEFT JOIN criteria_values cv ON p.key=cv.key LEFT JOIN
criteria c ON cv.key=c.key WHERE c.value IS NOT NULL
Hard to answer without more detail, but I am guessing the answer will be
something like the above. Your question makes it hard to understand whether c
or cv is joined to p. So swap em around if I misunderstood.
iPhone 4. It rocks!
On Nov 21, 2010, at 1:37 AM, Simcha Younger wrote:
> On Sat, 20 Nov 2010 13:54:29 -0700
> "Ben Miller" wrote:
>
>> Hi,
>>
>> I'm building a website for a client in which I need to compare their
>> products, side-by-side, but only include criteria for which all selected
>> products have a value for that criteria.
>>
>> In my database (MySQL), I have a tables named "products","criteria" and
>> "criteria_values"
>>
>> If I have something like
>>
>> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
>> products selected for comparison by the user
>>
>> I need to get only rows from "criteria" where there is a row in
>> "criteria_values" matching "criteria.criteria_id" for each $selected_product
>> - in other words, if any of the $selected_product does not have a row in
>> "criteria_values" that matches "criteria.criteria_id", that criteria would
>> not be returned. I hope that makes sense.
>
> It would be a lot easier to think about this if you could provide the table
> structure or create table statements.
>
> If I understood correctly, you have products which reference a criteria ID
> which has no matching value. If this is the problem you have a to first take
> care of the integrity of your data, as this should never happen.
>
>
> --
> Simcha Younger
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
On Sat, 20 Nov 2010 13:54:29 -0700
"Ben Miller" wrote:
> Hi,
>
> I'm building a website for a client in which I need to compare their
> products, side-by-side, but only include criteria for which all selected
> products have a value for that criteria.
>
> In my database (MySQL), I have a tables named "products","criteria" and
> "criteria_values"
>
> If I have something like
>
> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
> products selected for comparison by the user
>
> I need to get only rows from "criteria" where there is a row in
> "criteria_values" matching "criteria.criteria_id" for each $selected_product
> - in other words, if any of the $selected_product does not have a row in
> "criteria_values" that matches "criteria.criteria_id", that criteria would
> not be returned. I hope that makes sense.
It would be a lot easier to think about this if you could provide the table
structure or create table statements.
If I understood correctly, you have products which reference a criteria ID
which has no matching value. If this is the problem you have a to first take
care of the integrity of your data, as this should never happen.
--
Simcha Younger
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Query Help
Since we are just tossing out development environments.
We moved to Aptana in conjunction with TortoiseSVN for a team environment
development timelines dropped.
Personally I do not feel any gui editor makes you a better programmer, maybe
you understand the fundamentals a little less.
Not that anything so far has been an answer to your question.
Developing mysql statements that not only require minimum resources but that
are highly effective. Take a lot of trial and error.
I feel there is no better tool in my mind to test query concepts than
NaviCat.
Not only is the product very user friendly but as a senior developer it
gives me more insight into the impact the query has on my servers.
Always think longevity of the product you are producing.
Imagine one day you have 650,000 products how will the query impact the
service you have written.
I feel as a certified MySQL DBA you should understand that what works today
may NOT be the best choice in query statements for the future.
Always analyze your query statements for query length and system resources
requirements.
Depending on the structure of your database/tables/fields your query may be
achieved many different ways.
I might suggest you try an extended select statement.
"SELECT product from sometable WHERE product='$array1' AND product=(SELECT
product_name from sometable where other matching critera)";
By extending or what some may call concating the statement the return is
more effective.
Richard L. Buskirk
-Original Message-
From: Ben Miller [mailto:biprel...@gmail.com]
Sent: Saturday, November 20, 2010 3:54 PM
To: 'php-general'
Subject: [PHP] MySQL Query Help
Hi,
I'm building a website for a client in which I need to compare their
products, side-by-side, but only include criteria for which all selected
products have a value for that criteria.
In my database (MySQL), I have a tables named "products","criteria" and
"criteria_values"
If I have something like
$selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
products selected for comparison by the user
I need to get only rows from "criteria" where there is a row in
"criteria_values" matching "criteria.criteria_id" for each $selected_product
- in other words, if any of the $selected_product does not have a row in
"criteria_values" that matches "criteria.criteria_id", that criteria would
not be returned. I hope that makes sense.
I've played around with a few join queries, but none have given the desired
results. Best I've been able to come up with so far is to query "criteria"
for each DISTINCT(criteria_id) and then run through each $selected_product
to make sure each has a criteria_value with a matching criteria_id,
eliminating any criteria where the number of criteria_values <
count($selected_product), but this seems pretty inefficient.
Thanks in advance for any help.
Ben Miller
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
I'm going to jump in and throw in my 2 cents...
Have you used dreamweaver?
I would suggest Dreamweaver to any new programmer beginning php/mysql.
It helped me out tremendously in the beginning. I'm not an advanced programmer
with hand coding classes yet, but I can get any job completed for clients with
dreamweaver. Custom content management systems, image galleries from mysql
etc...
Give it a try, It lets you add the the prewritten code and then you can switch
to code view and see whats its doing.
RD
On Nov 20, 2010, at 3:54 PM, Ben Miller wrote:
> Hi,
>
> I'm building a website for a client in which I need to compare their
> products, side-by-side, but only include criteria for which all selected
> products have a value for that criteria.
>
> In my database (MySQL), I have a tables named "products","criteria" and
> "criteria_values"
>
> If I have something like
>
> $selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
> products selected for comparison by the user
>
> I need to get only rows from "criteria" where there is a row in
> "criteria_values" matching "criteria.criteria_id" for each $selected_product
> - in other words, if any of the $selected_product does not have a row in
> "criteria_values" that matches "criteria.criteria_id", that criteria would
> not be returned. I hope that makes sense.
>
> I've played around with a few join queries, but none have given the desired
> results. Best I've been able to come up with so far is to query "criteria"
> for each DISTINCT(criteria_id) and then run through each $selected_product
> to make sure each has a criteria_value with a matching criteria_id,
> eliminating any criteria where the number of criteria_values <
> count($selected_product), but this seems pretty inefficient.
>
> Thanks in advance for any help.
>
> Ben Miller
>
>
> --
> 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 Query Help
Hi,
I'm building a website for a client in which I need to compare their
products, side-by-side, but only include criteria for which all selected
products have a value for that criteria.
In my database (MySQL), I have a tables named "products","criteria" and
"criteria_values"
If I have something like
$selected_product = array("1"=>"Product 1","2"=>"Product 2"...) // All
products selected for comparison by the user
I need to get only rows from "criteria" where there is a row in
"criteria_values" matching "criteria.criteria_id" for each $selected_product
- in other words, if any of the $selected_product does not have a row in
"criteria_values" that matches "criteria.criteria_id", that criteria would
not be returned. I hope that makes sense.
I've played around with a few join queries, but none have given the desired
results. Best I've been able to come up with so far is to query "criteria"
for each DISTINCT(criteria_id) and then run through each $selected_product
to make sure each has a criteria_value with a matching criteria_id,
eliminating any criteria where the number of criteria_values <
count($selected_product), but this seems pretty inefficient.
Thanks in advance for any help.
Ben Miller
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
I am very keen to see a closure to this thread so that I can add to my snippets. Let's all know what worked best out of many solutions that have been proposed. --Shreyas On Tue, Jul 20, 2010 at 10:07 AM, Jim Lucas wrote: > Peter wrote: > >> Hi All, >> >> I have a table which contain's some duplicate rows. I just want to delete >> the duplicate records alone >> not original records. >> >> Assume my table as look as below >> >> column1 column2 >> 1 >>a >> 1 >>a >> 2 >>b >> 3 >>c >> 3 >>c >> >> >> >> i want the above table need to be as below, After executing the mysql >> query. >> >> column1 >>column2 >> 1 >>a >> 2 >>b >> 3 >>c >> >> >> >> >> Thanks in advance.. >> >> Regards >> Peter >> >> > Use the SQL command alter with the ignore flag. > > ALTER IGNORE TABLE `your_table` ADD UNIQUE ( `column1` , `column2` ) > > I tested this on my test DB and it worked fine. It erased all the > duplicates and left one instance of the multiple entry values. > > This will add a permanent unique restraint to the table. So, you will > never have dupps again. > > Jim Lucas > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
Peter wrote: Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c Thanks in advance.. Regards Peter Use the SQL command alter with the ignore flag. ALTER IGNORE TABLE `your_table` ADD UNIQUE ( `column1` , `column2` ) I tested this on my test DB and it worked fine. It erased all the duplicates and left one instance of the multiple entry values. This will add a permanent unique restraint to the table. So, you will never have dupps again. Jim Lucas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
Just to add more perspective : You will have a table with DISTINCT values. Drop the initial table (better take a back-up); copy from the temporary table which will have only DISTINCT values. Regards, Shreyas On Mon, Jul 19, 2010 at 7:58 PM, Shreyas Agasthya wrote: > How about this : > > CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20)); > INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE; > > Regards, > Shreyas > > On Mon, Jul 19, 2010 at 7:31 PM, Richard Quadling wrote: > >> On 19 July 2010 05:44, Peter wrote: >> > Hi All, >> > >> > I have a table which contain's some duplicate rows. I just want to >> delete >> > the duplicate records alone >> > not original records. >> > >> > Assume my table as look as below >> > >> > column1 column2 >> > 1 >> >a >> > 1 >> >a >> > 2 >> >b >> > 3 >> >c >> > 3 >> >c >> > >> > >> > >> > i want the above table need to be as below, After executing the mysql >> > query. >> > >> > column1 >> >column2 >> > 1 >> >a >> > 2 >> >b >> > 3 >> >c >> > >> > >> > >> > >> > Thanks in advance.. >> > >> > Regards >> > Peter >> > >> >> If your table had a db generated sequential unique identifier (an >> identity / autoinc), then something along these lines may be what you >> are looking for ... >> >> -- Delete everything except the UniqueIDs we want to keep. >> DELETE FROM >>Table >> WHERE >>UniqueID NOT IN >>( >>-- Just get the UniqueIDs we want to keep. >>SELECT >>UniqueID >>FROM >>( >>-- Get the earlist UniqueID for each Col1, Col2, >> pairing. >>SELECT >>Col1, >>Col2, >>MIN(UniqueID) AS UniqueID >>FROM >>Table >>GROUP BY >>Col1, >>Col2 >>) >>) >> >> UNTESTED >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > > > -- > Regards, > Shreyas Agasthya > -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
How about this : CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20)); INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE; Regards, Shreyas On Mon, Jul 19, 2010 at 7:31 PM, Richard Quadling wrote: > On 19 July 2010 05:44, Peter wrote: > > Hi All, > > > > I have a table which contain's some duplicate rows. I just want to > delete > > the duplicate records alone > > not original records. > > > > Assume my table as look as below > > > > column1 column2 > > 1 > >a > > 1 > >a > > 2 > >b > > 3 > >c > > 3 > >c > > > > > > > > i want the above table need to be as below, After executing the mysql > > query. > > > > column1 > >column2 > > 1 > >a > > 2 > >b > > 3 > >c > > > > > > > > > > Thanks in advance.. > > > > Regards > > Peter > > > > If your table had a db generated sequential unique identifier (an > identity / autoinc), then something along these lines may be what you > are looking for ... > > -- Delete everything except the UniqueIDs we want to keep. > DELETE FROM >Table > WHERE >UniqueID NOT IN >( >-- Just get the UniqueIDs we want to keep. >SELECT >UniqueID >FROM >( >-- Get the earlist UniqueID for each Col1, Col2, > pairing. >SELECT >Col1, >Col2, >MIN(UniqueID) AS UniqueID >FROM >Table >GROUP BY >Col1, >Col2 >) >) > > UNTESTED > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
On 19 July 2010 15:01, Richard Quadling wrote: > On 19 July 2010 05:44, Peter wrote: >> Hi All, >> >> I have a table which contain's some duplicate rows. I just want to delete >> the duplicate records alone >> not original records. >> >> Assume my table as look as below >> >> column1 column2 >> 1 >> a >> 1 >> a >> 2 >> b >> 3 >> c >> 3 >> c >> >> >> >> i want the above table need to be as below, After executing the mysql >> query. >> >> column1 >> column2 >> 1 >> a >> 2 >> b >> 3 >> c >> >> >> >> >> Thanks in advance.. >> Slightly more concise ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Get the earliest UniqueIDs for each Col1, Col2 pairing. SELECT MIN(UniqueID) FROM Table GROUP BY Col1, Col2 ) http://www.devx.com/tips/Tip/14665 DELETE Table FROM Table T1, Table T2 WHERE T1.Col1 = T2.Col1 AND T1.Col2 = T2.Col2 AND T1.UniqueID > T2.UniqueID http://www.cryer.co.uk/brian/sql/sql_delete_duplicates.htm etc. Many different ways. http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table Method 3 should be the fastest. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On 19 July 2010 05:44, Peter wrote: > Hi All, > > I have a table which contain's some duplicate rows. I just want to delete > the duplicate records alone > not original records. > > Assume my table as look as below > > column1 column2 > 1 > a > 1 > a > 2 > b > 3 > c > 3 > c > > > > i want the above table need to be as below, After executing the mysql > query. > > column1 > column2 > 1 > a > 2 > b > 3 > c > > > > > Thanks in advance.. > > Regards > Peter > If your table had a db generated sequential unique identifier (an identity / autoinc), then something along these lines may be what you are looking for ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Just get the UniqueIDs we want to keep. SELECT UniqueID FROM ( -- Get the earlist UniqueID for each Col1, Col2, pairing. SELECT Col1, Col2, MIN(UniqueID) AS UniqueID FROM Table GROUP BY Col1, Col2 ) ) UNTESTED -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
Hi Shiplu, Thanks for reply. Distinct function hide the duplicate records while we selecting the record through the Query i want to remove the duplicate entries in my table i need a Delete Query instead of Select Query shiplu wrote: Use distinct. SELECT DISTINCT COLUMN1, COLUMN2 FROM ... ... Shiplu Mokadd.im My talks, http://talk.cmyweb.net Follow me, http://twitter.com/shiplu SUST Programmers, http://groups.google.com/group/p2psust Innovation distinguishes bet ... ... (ask Steve Jobs the rest) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
Use distinct. SELECT DISTINCT COLUMN1, COLUMN2 FROM ... ... Shiplu Mokadd.im My talks, http://talk.cmyweb.net Follow me, http://twitter.com/shiplu SUST Programmers, http://groups.google.com/group/p2psust Innovation distinguishes bet ... ... (ask Steve Jobs the rest) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On Mon, Jul 19, 2010 at 10:44 AM, Peter wrote: > Hi All, > > I have a table which contain's some duplicate rows. I just want to delete > the duplicate records alone > not original records. > > Assume my table as look as below > > column1 column2 > 1 > a > 1 > a > 2 > b > 3 > c > 3 > c > > > > i want the above table need to be as below, After executing the mysql > query. > > column1 > column2 > 1 > a > 2 > b > 3 > c > > > > > Thanks in advance.. > > Regards > Peter > Create a table with similar structure and add UNIQUE INDEX on both columns. Execute the following query: INSERT IGNORE INTO NEW_TABLE (column1, column2) SELECT column1, column2 FROM OLD_TABLE This will give you distinct rows as required. -- Keep Smiling :-) Shafiq Rehman Blog: http://shafiq.pk, Twitter: http://twitter.com/shafiq -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On Mon, Jul 19, 2010 at 10:14:30AM +0530, Peter wrote: > Hi All, > > I have a table which contain's some duplicate rows. I just want to > delete the duplicate records alone > not original records. > > Assume my table as look as below > > column1 column2 > 1 > a > 1 > a > 2 > b > 3 > c > 3 > c > > > > i want the above table need to be as below, After executing the mysql > query. > > column1 > column2 > 1 > a > 2 > b > 3 > c > > If you're looking for a MySQL solution to this, this is the wrong list to ask the question on. In fact, I'd be surprised to find a MySQL query which would do this. For a PHP solution, you'll need to query MySQL for all the rows, in order by the column you want to use to kill duplicates. Then loop through the rows one at a time in PHP, checking the contents of that column against the last iteration. If they are the same, issue a DELETE command in MySQL. Continue the loop until all rows are exhausted. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL Query Puzzle
Hi All, I have a table which contain's some duplicate rows. I just want to delete the duplicate records alone not original records. Assume my table as look as below column1 column2 1 a 1 a 2 b 3 c 3 c i want the above table need to be as below, After executing the mysql query. column1 column2 1 a 2 b 3 c Thanks in advance.. Regards Peter
[PHP] mysql query returning slowly
This is from our application I enabled profile in mysql to determine why an update took 20seconds. As you can see MySQL reported no where near that amount of duration took place. Is there any way I can dig into php and determine why mysql client libs are so slow (this is not using mysqlnd but mysql-client-libs on CentOS using 5.3.2) 04/06/2010 14:54:54 20.6899s UPDATE `calls` SET `Result`='Busy' WHERE `CallID`='144786' | Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | -- | starting | 0.39 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | checking permissions | 0.08 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | Opening tables | 0.10 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | System lock | 0.05 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | Table lock | 0.06 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | init | 0.36 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | Updating | 0.99 | 0.001000 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | end | 0.23 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | query end| 0.04 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | freeing items| 0.007410 | 0.00 | 0.00 | 4 | 1 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | logging slow query | 0.04 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | | cleaning up | 0.04 | 0.00 | 0.00 | 0 | 0 | 0| 0 | 0 | 0 | 0 | 0 | 0 | Thanks David Murphy
Re: [PHP] MySQL query not working!
At 4:20 PM +0430 3/31/10, Parham Doustdar wrote:
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than the
file starting to download. This is supposed to be a file download counter:
[code]
http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = "insert into " . $table . " (filename, hits) values ('" .
$_GET['file'] . "', 1)";
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
Hi Parham:
Considering that no one made comment, let me say that using $_GET in
such a fashion is dangerous. One should always clean/scrub all
variables that makeup a db query.
Doing what you did above is opening your database to possible SQL
injection. This is not a secure thing to do.
For example, let's say I provide the following string to your form (first GET):
"anything OR '1' = '1'; DROP TABLE customers"
If your database configuration allows for multiple statements, then
any table named "customers" would be immediately dropped from your
database. I'm sure you can see how you would not want to allow
someone to drop tables from your database. In short, never trust
anything coming from client-side.
Here's a reference on the subject:
http://en.wikipedia.org/wiki/SQL_injection
There are many others.
Cheers,
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
Re: [PHP] MySQL query not working!
On Wed, Mar 31, 2010 at 9:08 AM, Andrew Ballard wrote:
> Nope. All it does is suppress the error message. Just try it:
>
>
> @mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
> not connect');
>
> ?>
>
> Output:
> Could not connect
>
> @mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
> not connect');
>
> ?>
>
> Output:
>
> Warning: mysql_connect() [ href='function.mysql-connect'>function.mysql-connect]: Can't
> connect to MySQL server on 'localhost' (10061) in PHPDocument1
> on line 3
> Could not connect
>
> Andrew
>
OK, for the sake of the archives, I wish there was an "EDIT" feature
to this list. Copy/paste will get you every time; or, "Insanity: doing
the same thing over and over again and expecting different results."
:-)
At any rate, the point I was TRYING to make is correct, even if the
example wasn't quite right.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
hey Andrew ,
you are correct thanks for pointing tht.. i should have checked it
before so @ just prevents the warnings and errors from showing up
Midhun Girish
On Wed, Mar 31, 2010 at 6:38 PM, Andrew Ballard wrote:
> On Wed, Mar 31, 2010 at 8:46 AM, Ashley Sheridan
> wrote:
> > On Wed, 2010-03-31 at 16:50 +0430, Parham Doustdar wrote:
> >
> >> Andre,
> >> The @ operator is used for error catching statements. When you put:
> >>
> >> @mysql_connect('localhost', 'username', 'password') or die('Could not
> >> connect.');
> >>
> >> If PHP fails to make a connection, the script execution is stopped, and
> the
> >> error message between the apostrophes is given.
> >>
> >> And, I found out what the problem was; I should have put:
> >>
> >> if (mysql_num_rows($result))
> >>
> >> rather than just
> >>
> >> if ($result)
> >>
> >> Thanks!
> >> - Original Message -
> >> From: "Andre Polykanine"
> >> To: "Parham Doustdar"
> >> Cc:
> >> Sent: Wednesday, March 31, 2010 4:41 PM
> >> Subject: Re: [PHP] MySQL query not working!
> >>
> >>
> >> > Hello Parham,
> >> >
> >> > Adding to Ash's question, why to use the @ operator before
> >> > mysql_query?
> >> > --
> >> > With best regards from Ukraine,
> >> > Andre
> >> > Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon
> @
> >> > jabber.org
> >> > Yahoo! messenger: andre.polykanine; ICQ: 191749952
> >> > Twitter: m_elensule
> >> >
> >> > - Original message -
> >> > From: Parham Doustdar
> >> > To: php-general@lists.php.net
> >> > Date: Wednesday, March 31, 2010, 2:50:07 PM
> >> > Subject: [PHP] MySQL query not working!
> >> >
> >> > Hi there,
> >> > Here is a snippet of code... that doesn't work for some reason. Please
> >> > note
> >> > that I have put some
> >> >
> >> > @mysql_query($query) or die(mysql_error());
> >> >
> >> > statements, to see if MySQL gives an error. I receive nothing other
> than
> >> > the
> >> > file starting to download. This is supposed to be a file download
> counter:
> >> >
> >> > [code]
> >> > >> > //connect to the DB
> >> > mysql_connect() //There is no problem with the connection so I didn't
> >> > include the complete code.
> >> >
> >> > //The table where the hits are stored.
> >> > $table = "files";
> >> >
> >> > $query = "select * from " . $table . " where filename = '" .
> $_GET['file']
> >> > .
> >> > "'";
> >> > $result = mysql_query($query);
> >> >
> >> > if ($result) //Has the file previously been added?
> >> > {
> >> > $query = "update " . $table . " set hits = hits + 1 where filename =
> '" .
> >> > $_GET['file'] . "'";
> >> > @mysql_query($query) or die(mysql_error());
> >> > header('location:http://www.qwitter-client.net/' . $_GET['file']);
> >> > }
> >> > else //it's the first time we're adding this file to the DB.
> >> > {
> >> > $query = "insert into " . $table . " (filename, hits) values ('" .
> >> > $_GET['file'] . "', 1)";
> >> > @mysql_query($query) or die(mysql_error());
> >> > header('location:http://www.qwitter-client.net/' . $_GET['file']);
> >> > }
> >> > ?>
> >> >
> >> >
> >> >
> >> > --
> >> > PHP General Mailing List (http://www.php.net/)
> >> > To unsubscribe, visit: http://www.php.net/unsub.php
> >> >
> >>
> >>
> >
> >
> > My understanding of the @ here would be that PHP won't register the
> > error, so it won't ever die()
> >
> > Thanks,
> > Ash
> > http://www.ashleysheridan.co.uk
> >
> >
> >
>
> Nope. All it does is suppress the error message. Just try it:
>
>
> @mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
> not connect');
>
> ?>
>
> Output:
> Could not connect
>
> @mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
> not connect');
>
> ?>
>
> Output:
>
> Warning: mysql_connect() [ href='function.mysql-connect'>function.mysql-connect]: Can't
> connect to MySQL server on 'localhost' (10061) in PHPDocument1
> on line 3
> Could not connect
>
> Andrew
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
Re: [PHP] MySQL query not working!
On Wed, Mar 31, 2010 at 8:46 AM, Ashley Sheridan
wrote:
> On Wed, 2010-03-31 at 16:50 +0430, Parham Doustdar wrote:
>
>> Andre,
>> The @ operator is used for error catching statements. When you put:
>>
>> @mysql_connect('localhost', 'username', 'password') or die('Could not
>> connect.');
>>
>> If PHP fails to make a connection, the script execution is stopped, and the
>> error message between the apostrophes is given.
>>
>> And, I found out what the problem was; I should have put:
>>
>> if (mysql_num_rows($result))
>>
>> rather than just
>>
>> if ($result)
>>
>> Thanks!
>> ----- Original Message -
>> From: "Andre Polykanine"
>> To: "Parham Doustdar"
>> Cc:
>> Sent: Wednesday, March 31, 2010 4:41 PM
>> Subject: Re: [PHP] MySQL query not working!
>>
>>
>> > Hello Parham,
>> >
>> > Adding to Ash's question, why to use the @ operator before
>> > mysql_query?
>> > --
>> > With best regards from Ukraine,
>> > Andre
>> > Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
>> > jabber.org
>> > Yahoo! messenger: andre.polykanine; ICQ: 191749952
>> > Twitter: m_elensule
>> >
>> > - Original message -
>> > From: Parham Doustdar
>> > To: php-general@lists.php.net
>> > Date: Wednesday, March 31, 2010, 2:50:07 PM
>> > Subject: [PHP] MySQL query not working!
>> >
>> > Hi there,
>> > Here is a snippet of code... that doesn't work for some reason. Please
>> > note
>> > that I have put some
>> >
>> > @mysql_query($query) or die(mysql_error());
>> >
>> > statements, to see if MySQL gives an error. I receive nothing other than
>> > the
>> > file starting to download. This is supposed to be a file download counter:
>> >
>> > [code]
>> > > > //connect to the DB
>> > mysql_connect() //There is no problem with the connection so I didn't
>> > include the complete code.
>> >
>> > //The table where the hits are stored.
>> > $table = "files";
>> >
>> > $query = "select * from " . $table . " where filename = '" . $_GET['file']
>> > .
>> > "'";
>> > $result = mysql_query($query);
>> >
>> > if ($result) //Has the file previously been added?
>> > {
>> > $query = "update " . $table . " set hits = hits + 1 where filename = '" .
>> > $_GET['file'] . "'";
>> > @mysql_query($query) or die(mysql_error());
>> > header('location:http://www.qwitter-client.net/' . $_GET['file']);
>> > }
>> > else //it's the first time we're adding this file to the DB.
>> > {
>> > $query = "insert into " . $table . " (filename, hits) values ('" .
>> > $_GET['file'] . "', 1)";
>> > @mysql_query($query) or die(mysql_error());
>> > header('location:http://www.qwitter-client.net/' . $_GET['file']);
>> > }
>> > ?>
>> >
>> >
>> >
>> > --
>> > PHP General Mailing List (http://www.php.net/)
>> > To unsubscribe, visit: http://www.php.net/unsub.php
>> >
>>
>>
>
>
> My understanding of the @ here would be that PHP won't register the
> error, so it won't ever die()
>
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
>
>
Nope. All it does is suppress the error message. Just try it:
Output:
Could not connect
Output:
Warning: mysql_connect() [function.mysql-connect]: Can't
connect to MySQL server on 'localhost' (10061) in PHPDocument1
on line 3
Could not connect
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
Yes ash.. me too think the same... @ will supress any error which would have
lead to die()... so die() wont come ever
Midhun Girish
On Wed, Mar 31, 2010 at 6:16 PM, Ashley Sheridan
wrote:
> On Wed, 2010-03-31 at 16:50 +0430, Parham Doustdar wrote:
>
> > Andre,
> > The @ operator is used for error catching statements. When you put:
> >
> > @mysql_connect('localhost', 'username', 'password') or die('Could not
> > connect.');
> >
> > If PHP fails to make a connection, the script execution is stopped, and
> the
> > error message between the apostrophes is given.
> >
> > And, I found out what the problem was; I should have put:
> >
> > if (mysql_num_rows($result))
> >
> > rather than just
> >
> > if ($result)
> >
> > Thanks!
> > - Original Message -
> > From: "Andre Polykanine"
> > To: "Parham Doustdar"
> > Cc:
> > Sent: Wednesday, March 31, 2010 4:41 PM
> > Subject: Re: [PHP] MySQL query not working!
> >
> >
> > > Hello Parham,
> > >
> > > Adding to Ash's question, why to use the @ operator before
> > > mysql_query?
> > > --
> > > With best regards from Ukraine,
> > > Andre
> > > Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon
> @
> > > jabber.org
> > > Yahoo! messenger: andre.polykanine; ICQ: 191749952
> > > Twitter: m_elensule
> > >
> > > - Original message -
> > > From: Parham Doustdar
> > > To: php-general@lists.php.net
> > > Date: Wednesday, March 31, 2010, 2:50:07 PM
> > > Subject: [PHP] MySQL query not working!
> > >
> > > Hi there,
> > > Here is a snippet of code... that doesn't work for some reason. Please
> > > note
> > > that I have put some
> > >
> > > @mysql_query($query) or die(mysql_error());
> > >
> > > statements, to see if MySQL gives an error. I receive nothing other
> than
> > > the
> > > file starting to download. This is supposed to be a file download
> counter:
> > >
> > > [code]
> > > > > //connect to the DB
> > > mysql_connect() //There is no problem with the connection so I didn't
> > > include the complete code.
> > >
> > > //The table where the hits are stored.
> > > $table = "files";
> > >
> > > $query = "select * from " . $table . " where filename = '" .
> $_GET['file']
> > > .
> > > "'";
> > > $result = mysql_query($query);
> > >
> > > if ($result) //Has the file previously been added?
> > > {
> > > $query = "update " . $table . " set hits = hits + 1 where filename = '"
> .
> > > $_GET['file'] . "'";
> > > @mysql_query($query) or die(mysql_error());
> > > header('location:http://www.qwitter-client.net/' . $_GET['file']);
> > > }
> > > else //it's the first time we're adding this file to the DB.
> > > {
> > > $query = "insert into " . $table . " (filename, hits) values ('" .
> > > $_GET['file'] . "', 1)";
> > > @mysql_query($query) or die(mysql_error());
> > > header('location:http://www.qwitter-client.net/' . $_GET['file']);
> > > }
> > > ?>
> > >
> > >
> > >
> > > --
> > > PHP General Mailing List (http://www.php.net/)
> > > To unsubscribe, visit: http://www.php.net/unsub.php
> > >
> >
> >
>
>
> My understanding of the @ here would be that PHP won't register the
> error, so it won't ever die()
>
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
>
>
Re: [PHP] MySQL query not working!
On Wed, 2010-03-31 at 16:50 +0430, Parham Doustdar wrote:
> Andre,
> The @ operator is used for error catching statements. When you put:
>
> @mysql_connect('localhost', 'username', 'password') or die('Could not
> connect.');
>
> If PHP fails to make a connection, the script execution is stopped, and the
> error message between the apostrophes is given.
>
> And, I found out what the problem was; I should have put:
>
> if (mysql_num_rows($result))
>
> rather than just
>
> if ($result)
>
> Thanks!
> - Original Message -
> From: "Andre Polykanine"
> To: "Parham Doustdar"
> Cc:
> Sent: Wednesday, March 31, 2010 4:41 PM
> Subject: Re: [PHP] MySQL query not working!
>
>
> > Hello Parham,
> >
> > Adding to Ash's question, why to use the @ operator before
> > mysql_query?
> > --
> > With best regards from Ukraine,
> > Andre
> > Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
> > jabber.org
> > Yahoo! messenger: andre.polykanine; ICQ: 191749952
> > Twitter: m_elensule
> >
> > - Original message -
> > From: Parham Doustdar
> > To: php-general@lists.php.net
> > Date: Wednesday, March 31, 2010, 2:50:07 PM
> > Subject: [PHP] MySQL query not working!
> >
> > Hi there,
> > Here is a snippet of code... that doesn't work for some reason. Please
> > note
> > that I have put some
> >
> > @mysql_query($query) or die(mysql_error());
> >
> > statements, to see if MySQL gives an error. I receive nothing other than
> > the
> > file starting to download. This is supposed to be a file download counter:
> >
> > [code]
> > > //connect to the DB
> > mysql_connect() //There is no problem with the connection so I didn't
> > include the complete code.
> >
> > //The table where the hits are stored.
> > $table = "files";
> >
> > $query = "select * from " . $table . " where filename = '" . $_GET['file']
> > .
> > "'";
> > $result = mysql_query($query);
> >
> > if ($result) //Has the file previously been added?
> > {
> > $query = "update " . $table . " set hits = hits + 1 where filename = '" .
> > $_GET['file'] . "'";
> > @mysql_query($query) or die(mysql_error());
> > header('location:http://www.qwitter-client.net/' . $_GET['file']);
> > }
> > else //it's the first time we're adding this file to the DB.
> > {
> > $query = "insert into " . $table . " (filename, hits) values ('" .
> > $_GET['file'] . "', 1)";
> > @mysql_query($query) or die(mysql_error());
> > header('location:http://www.qwitter-client.net/' . $_GET['file']);
> > }
> > ?>
> >
> >
> >
> > --
> > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
>
>
My understanding of the @ here would be that PHP won't register the
error, so it won't ever die()
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re[2]: [PHP] MySQL query not working!
Hello Parham,
I know what the @ operator does (it stops PHP from reporting errors
and makes it ignore error_reporting() or any INI directives) but I
don't understand why to use it here, with mysql_query() function.
--
With best regards from Ukraine,
Andre
Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar
To: Andre Polykanine
Date: Wednesday, March 31, 2010, 3:20:54 PM
Subject: [PHP] MySQL query not working!
Andre,
The @ operator is used for error catching statements. When you put:
@mysql_connect('localhost', 'username', 'password') or die('Could not
connect.');
If PHP fails to make a connection, the script execution is stopped, and the
error message between the apostrophes is given.
And, I found out what the problem was; I should have put:
if (mysql_num_rows($result))
rather than just
if ($result)
Thanks!
- Original Message -
From: "Andre Polykanine"
To: "Parham Doustdar"
Cc:
Sent: Wednesday, March 31, 2010 4:41 PM
Subject: Re: [PHP] MySQL query not working!
> Hello Parham,
>
> Adding to Ash's question, why to use the @ operator before
> mysql_query?
> --
> With best regards from Ukraine,
> Andre
> Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
> jabber.org
> Yahoo! messenger: andre.polykanine; ICQ: 191749952
> Twitter: m_elensule
>
> - Original message -
> From: Parham Doustdar
> To: php-general@lists.php.net
> Date: Wednesday, March 31, 2010, 2:50:07 PM
> Subject: [PHP] MySQL query not working!
>
> Hi there,
> Here is a snippet of code... that doesn't work for some reason. Please
> note
> that I have put some
>
> @mysql_query($query) or die(mysql_error());
>
> statements, to see if MySQL gives an error. I receive nothing other than
> the
> file starting to download. This is supposed to be a file download counter:
>
> [code]
> //connect to the DB
> mysql_connect() //There is no problem with the connection so I didn't
> include the complete code.
>
> //The table where the hits are stored.
> $table = "files";
>
> $query = "select * from " . $table . " where filename = '" . $_GET['file']
> .
> "'";
> $result = mysql_query($query);
>
> if ($result) //Has the file previously been added?
> {
> $query = "update " . $table . " set hits = hits + 1 where filename = '" .
> $_GET['file'] . "'";
> @mysql_query($query) or die(mysql_error());
> header('location:http://www.qwitter-client.net/' . $_GET['file']);
> }
> else //it's the first time we're adding this file to the DB.
> {
> $query = "insert into " . $table . " (filename, hits) values ('" .
> $_GET['file'] . "', 1)";
> @mysql_query($query) or die(mysql_error());
> header('location:http://www.qwitter-client.net/' . $_GET['file']);
> }
> ?>
>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
Hi!
To view error:
you must use mysql_query(). @ before mysql - error supression.
next
you can use the following:
$result=..
if($result){
if(mysql_num_rows($result)){
/* you have record in table */
}else{
/* you haven't */
On Wed, Mar 31, 2010 at 4:11 PM, Andre Polykanine wrote:
> Hello Parham,
>
> Adding to Ash's question, why to use the @ operator before
> mysql_query?
> --
> With best regards from Ukraine,
> Andre
> Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
> jabber.org
> Yahoo! messenger: andre.polykanine; ICQ: 191749952
> Twitter: m_elensule
>
> - Original message -
> From: Parham Doustdar
> To: php-general@lists.php.net
> Date: Wednesday, March 31, 2010, 2:50:07 PM
> Subject: [PHP] MySQL query not working!
>
> Hi there,
> Here is a snippet of code... that doesn't work for some reason. Please note
> that I have put some
>
> @mysql_query($query) or die(mysql_error());
>
> statements, to see if MySQL gives an error. I receive nothing other than
> the
> file starting to download. This is supposed to be a file download counter:
>
> [code]
> //connect to the DB
> mysql_connect() //There is no problem with the connection so I didn't
> include the complete code.
>
> //The table where the hits are stored.
> $table = "files";
>
> $query = "select * from " . $table . " where filename = '" . $_GET['file']
> .
> "'";
> $result = mysql_query($query);
>
> if ($result) //Has the file previously been added?
> {
> $query = "update " . $table . " set hits = hits + 1 where filename = '" .
> $_GET['file'] . "'";
> @mysql_query($query) or die(mysql_error());
> header('location:http://www.qwitter-client.net/' . $_GET['file']);
> }
> else //it's the first time we're adding this file to the DB.
> {
> $query = "insert into " . $table . " (filename, hits) values ('" .
> $_GET['file'] . "', 1)";
> @mysql_query($query) or die(mysql_error());
> header('location:http://www.qwitter-client.net/' . $_GET['file']);
> }
> ?>
>
>
>
> --
> 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
>
>
--
With regards,
Alexei Bovanenko
Re: [PHP] MySQL query not working!
Andre,
The @ operator is used for error catching statements. When you put:
@mysql_connect('localhost', 'username', 'password') or die('Could not
connect.');
If PHP fails to make a connection, the script execution is stopped, and the
error message between the apostrophes is given.
And, I found out what the problem was; I should have put:
if (mysql_num_rows($result))
rather than just
if ($result)
Thanks!
- Original Message -
From: "Andre Polykanine"
To: "Parham Doustdar"
Cc:
Sent: Wednesday, March 31, 2010 4:41 PM
Subject: Re: [PHP] MySQL query not working!
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar
To: php-general@lists.php.net
Date: Wednesday, March 31, 2010, 2:50:07 PM
Subject: [PHP] MySQL query not working!
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please
note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than
the
file starting to download. This is supposed to be a file download counter:
[code]
$query = "select * from " . $table . " where filename = '" . $_GET['file']
.
"'";
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = "update " . $table . " set hits = hits + 1 where filename = '" .
$_GET['file'] . "'";
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = "insert into " . $table . " (filename, hits) values ('" .
$_GET['file'] . "', 1)";
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; Wlm&MSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar
To: php-general@lists.php.net
Date: Wednesday, March 31, 2010, 2:50:07 PM
Subject: [PHP] MySQL query not working!
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than the
file starting to download. This is supposed to be a file download counter:
[code]
http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = "insert into " . $table . " (filename, hits) values ('" .
$_GET['file'] . "', 1)";
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
On Wed, 2010-03-31 at 16:20 +0430, Parham Doustdar wrote:
> Hi there,
> Here is a snippet of code... that doesn't work for some reason. Please note
> that I have put some
>
> @mysql_query($query) or die(mysql_error());
>
> statements, to see if MySQL gives an error. I receive nothing other than the
> file starting to download. This is supposed to be a file download counter:
>
> [code]
> //connect to the DB
> mysql_connect() //There is no problem with the connection so I didn't
> include the complete code.
>
> //The table where the hits are stored.
> $table = "files";
>
> $query = "select * from " . $table . " where filename = '" . $_GET['file'] .
> "'";
> $result = mysql_query($query);
>
> if ($result) //Has the file previously been added?
> {
> $query = "update " . $table . " set hits = hits + 1 where filename = '" .
> $_GET['file'] . "'";
> @mysql_query($query) or die(mysql_error());
> header('location:http://www.qwitter-client.net/' . $_GET['file']);
> }
> else //it's the first time we're adding this file to the DB.
> {
> $query = "insert into " . $table . " (filename, hits) values ('" .
> $_GET['file'] . "', 1)";
> @mysql_query($query) or die(mysql_error());
> header('location:http://www.qwitter-client.net/' . $_GET['file']);
> }
> ?>
>
>
>
What is the output of $query?
Thanks,
Ash
http://www.ashleysheridan.co.uk
[PHP] MySQL query not working!
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than the
file starting to download. This is supposed to be a file download counter:
[code]
http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = "insert into " . $table . " (filename, hits) values ('" .
$_GET['file'] . "', 1)";
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] php/mysql Query Question.
ad...@buskirkgraphics.com wrote:
> Before most of you go on a rampage of how to please read below...
>
> As most of you already know when using MySQL from the shell you can write
> your queries in html format in an out file.
>
> Example: shell>mysql -uyourmom -plovesme --html
> This now will return all results in an html format from all queries.
>
> Now I could “tee” this to a file and save the results returned if I so choose
> to save the result of the display .
>
> Let’s say I want to be lazy and write a php MySQL query to do the same so
> that any result I queried for would return the html results in a table
> without actually writing the table tags in the results.
>
> Is there a mysql_connect or select_db or mysql_query tag option to do that
> since mysql can display it from the shell?
>
Here is my rendition of an result to HTML table output function.
This is normally used within my db class
but I have modified it to work with a MySQLi result object
function debug($result) {
/* get column metadata */
$html = '';
foreach ( $result->fetch_fields() AS $val ) {
$html .= ''.$val->name.'';
}
$html .= '';
foreach ( $result->fetch_row() AS $row ) {
$html .= '';
foreach ( $row AS $value ) {
$html .= ' '.$value.'';
}
$html .= '';
}
$html .= '';
return $html;
}
Let us know if that works for you.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] php/mysql Query Question.
ad...@buskirkgraphics.com wrote: I tend to do this robert, while looking at your example i thought to myself since i am trying to mimick a shell command why not run one. Result: $ddvery"; ?> Not are the results safe but the unlimited possibilites are amazing. Thanks so much for the kick starter This presumes your information is all safe and that there are no special shell characters in any of the configuration settings (now and in the future). Also, the shell_exec() function is "identical" to the backtick operator that I used in my example (see the help). You've essentially done what I did, but made it less robust... except for the use of the --execute parameter which I wasn't aware existed since it's just as easy to pipe :) Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] php/mysql Query Question.
I tend to do this robert, while looking at your example i thought to myself since i am trying to mimick a shell command why not run one. Result: $ddvery"; ?> Not are the results safe but the unlimited possibilites are amazing. Thanks so much for the kick starter ad...@buskirkgraphics.com wrote: > Would you mind giving me an example of this that i can stick right into a blank php file and run. > > I get what you are saying but i cant seem to make that even echo out the data. php 5.2 mysql 5.1.3 Apache 2.2 Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Re: php/mysql Query Question.
ad...@buskirkgraphics.com wrote: > Before most of you go on a rampage of how to please read below... > > As most of you already know when using MySQL from the shell you can write > your queries in html format in an out file. > > Example: shell>mysql -uyourmom -plovesme --html > This now will return all results in an html format from all queries. > > Now I could “tee” this to a file and save the results returned if I so choose > to save the result of the display . > > Let’s say I want to be lazy and write a php MySQL query to do the same so > that any result I queried for would return the html results in a table > without actually writing the table tags in the results. > > Is there a mysql_connect or select_db or mysql_query tag option to do that > since mysql can display it from the shell? I think you'll find that the HTML output is a function of the mysql command line program (I tend to use PostgreSQL, where 'psql' is a similar program) so you can only access that functionality by calling the command line. I suspect that, since PHP is a HTML processing language (originally), the creators of the mysql_ functions figured that the user could sort out making HTML from the data returned... It's should be a simple operation to write a wrapper function to put HTML around the results. There might even be a PEAR extension or PHPClasses class to do it (I haven't looked yet) -- Peter Ford phone: 01580 89 Developer fax: 01580 893399 Justcroft International Ltd., Staplehurst, Kent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] php/mysql Query Question.
ad...@buskirkgraphics.com wrote: Would you mind giving me an example of this that i can stick right into a blank php file and run. I get what you are saying but i cant seem to make that even echo out the data. php 5.2 mysql 5.1.3 Apache 2.2 Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] php/mysql Query Question.
ad...@buskirkgraphics.com wrote: Before most of you go on a rampage of how to please read below... As most of you already know when using MySQL from the shell you can write your queries in html format in an out file. Example: shell>mysql -uyourmom -plovesme --html This now will return all results in an html format from all queries. Now I could “tee” this to a file and save the results returned if I so choose to save the result of the display . Let’s say I want to be lazy and write a php MySQL query to do the same so that any result I queried for would return the html results in a table without actually writing the table tags in the results. Is there a mysql_connect or select_db or mysql_query tag option to do that since mysql can display it from the shell? echo "Select * from my_table" | mysql --html -ufoo -pfee database_name However, this allows for your database password to be visible in the process list for a brief moment of time. You might be better served by finer grained process control where you can check the output and provide input as needed. Or a simple expect script might suffice. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] php/mysql Query Question.
Before most of you go on a rampage of how to please read below... As most of you already know when using MySQL from the shell you can write your queries in html format in an out file. Example: shell>mysql -uyourmom -plovesme --html This now will return all results in an html format from all queries. Now I could “tee” this to a file and save the results returned if I so choose to save the result of the display . Let’s say I want to be lazy and write a php MySQL query to do the same so that any result I queried for would return the html results in a table without actually writing the table tags in the results. Is there a mysql_connect or select_db or mysql_query tag option to do that since mysql can display it from the shell? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Chris wrote: Jim Lucas wrote: [EMAIL PROTECTED] wrote: Ok, so just that I am clear, you are SELECTing and pulling all the data that you are submitting in the above INSERT statement from the DB initially, then you are only modifying the confirm_number value and then re- submitting all the values, as they originally were, Well, actually when all is said and done, a new record will be created with new information (Name, phone, email, etc) and the confirm_number is the previous+1 Seems like a perfect candidate for an auto-inc field, though mysql doesn't let you have multiple in the same table (afaik). I would agree, but I'm not the OP. He/She wanted it this way... You do have a race condition, you can end up with 2 of the same confirm_numbers (you'd have to be unlucky, but it can happen). 2 hits at the same time = 2 selects getting the same max(confirm_number), which results in 2 inserts with the same number. Granted that their is a possibility that it could happen. But the chance of it happening with the three statements running back-to-back in the same call is much lower then having three separate calls and doing the math in PHP. -- Jim Lucas "Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them." Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Jim Lucas wrote:
[EMAIL PROTECTED] wrote:
Ok, so just that I am clear, you are SELECTing and pulling all the data
that you are submitting in the above INSERT statement from the DB
initially,
then you are only modifying the confirm_number value and then re-
submitting all the values, as they originally were,
Well, actually when all is said and done, a new record will be created with
new information (Name, phone, email, etc) and the confirm_number is the
previous+1
Seems like a perfect candidate for an auto-inc field, though mysql
doesn't let you have multiple in the same table (afaik).
# Now prepare your statement
$SQL = "
SET @confirm_number=(SELECT (MAX(confirm_number)+1) FROM `contacts`);
INSERT INTO `contacts` (
`first_name`,
`last_name`,
`email`,
`phn_number`,
`address`,
`city`,
`state`,
`zip`,
`dates`,
`comments`,
`confirm_number`
) VALUES (
'{$FirstName}',
'{$LastName}',
'{$Email}',
'{$Phone}',
'{$Address}',
'{$City}',
'{$selected_state}',
'{$Zip}',
'{$newdate}',
'{$Comments}',
@confirm_number
)
SELECT @confirm_number AS confirm_number;
";
You do have a race condition, you can end up with 2 of the same
confirm_numbers (you'd have to be unlucky, but it can happen).
2 hits at the same time = 2 selects getting the same
max(confirm_number), which results in 2 inserts with the same number.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Michael S. Dunsavage wrote: On Fri, 2008-11-14 at 12:46 -0800, Jim Lucas wrote: SELECT @confirm_number AS confirm_number; Are we not SELECTING the column value here? should we be selecting confirm_number as confirm_number? The idea is to give you the number that was used in the INSERT statement. It might have changed since the INSERT. Never know. So, giving you the one used in the INSERT is the best way to make sure you get the one you are expecting. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 12:46 -0800, Jim Lucas wrote: > SELECT @confirm_number AS confirm_number; Are we not SELECTING the column value here? should we be selecting confirm_number as confirm_number? -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Jim Lucas wrote:
> Michael S. Dunsavage wrote:
>> On Fri, 2008-11-14 at 13:31 -0800, Jim Lucas wrote:
'{$Comments}',
@confirm_number
)
>>> The above should be this instead
>>>
>>> @confirm_number
>>> );
>> Even after fixing that, nothing gets inserted into the database. I've
>> been all over the variables and column names and the naming is correct.
>
> Take the @ off the mysql_query() and also check into mysql_error() function.
>
also, try it with a striped down version of the insert, just inserting the
confirm_number
INSERT INTO contacts (confirm_number) VALUES (@confirm_number);
and see if that creates a new record.
--
Jim Lucas
"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Michael S. Dunsavage wrote:
> On Fri, 2008-11-14 at 13:31 -0800, Jim Lucas wrote:
>>> '{$Comments}',
>>> @confirm_number
>>> )
>> The above should be this instead
>>
>> @confirm_number
>> );
>
> Even after fixing that, nothing gets inserted into the database. I've
> been all over the variables and column names and the naming is correct.
Take the @ off the mysql_query() and also check into mysql_error() function.
--
Jim Lucas
"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 13:31 -0800, Jim Lucas wrote:
> > '{$Comments}',
> > @confirm_number
> > )
>
> The above should be this instead
>
> @confirm_number
> );
Even after fixing that, nothing gets inserted into the database. I've
been all over the variables and column names and the naming is correct.
--
Michael S. Dunsavage
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Jim Lucas wrote:
> [EMAIL PROTECTED] wrote:
>>> Ok, so just that I am clear, you are SELECTing and pulling all the data
>>> that you are submitting in the above INSERT statement from the DB
>>> initially,
>>> then you are only modifying the confirm_number value and then re-
>>> submitting all the values, as they originally were,
>> Well, actually when all is said and done, a new record will be created with
>> new information (Name, phone, email, etc) and the confirm_number is the
>> previous+1
>>
>>
>> This whole thing is a contact form.
>>
>
> Well, in that case, you might be able to do something along the lines of this.
>
> I tested this on my server:
> Server version: 5.0.51a-log
> MySQL client version: 5.0.51a
> using phpMyAdmin - 2.11.1.2
>
> I have modified an example from this page:
> http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
>
> #
> # Setup database stuff, process input, get everything ready to do the insert.
> #
>
> # Now prepare your statement
> $SQL = "
> SET @confirm_number=(SELECT (MAX(confirm_number)+1) FROM `contacts`);
> INSERT INTO `contacts` (
> `first_name`,
> `last_name`,
> `email`,
> `phn_number`,
> `address`,
> `city`,
> `state`,
> `zip`,
> `dates`,
> `comments`,
> `confirm_number`
> ) VALUES (
> '{$FirstName}',
> '{$LastName}',
> '{$Email}',
> '{$Phone}',
> '{$Address}',
> '{$City}',
> '{$selected_state}',
> '{$Zip}',
> '{$newdate}',
> '{$Comments}',
> @confirm_number
> )
The above should be this instead
@confirm_number
);
> SELECT @confirm_number AS confirm_number;
> ";
> $confirm_number = NULL;
> # Run it and get confirm_number to work with now.
> if ( ($result = @mysql_query($SQL)) !== FALSE ) {
> list($confirm_number) = mysql_fetch_row($result);
> }
>
> if ( is_null($confirm_number) ) {
> echo 'Failed to get number';
> }
>
> ?>
>
> Obviously, I can't test this without your schema. So, I hope it works.
>
> In the end, you should have a result set that gets returned that contains the
> 'confirm_number' of the newly created entry.
>
> This should also, pretty much, eliminate any chance of a race condition.
> Since everything is happening within mysql, it should be very hard to end up
> with a condition that you start stomping on records.
>
> Let the list know if it works for you.
>
--
Jim Lucas
"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
[EMAIL PROTECTED] wrote: >> Ok, so just that I am clear, you are SELECTing and pulling all the data >> that you are submitting in the above INSERT statement from the DB >> initially, >> then you are only modifying the confirm_number value and then re- >> submitting all the values, as they originally were, > > Well, actually when all is said and done, a new record will be created with > new information (Name, phone, email, etc) and the confirm_number is the > previous+1 > > > This whole thing is a contact form. > Well, in that case, you might be able to do something along the lines of this. I tested this on my server: Server version: 5.0.51a-log MySQL client version: 5.0.51a using phpMyAdmin - 2.11.1.2 I have modified an example from this page: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html Obviously, I can't test this without your schema. So, I hope it works. In the end, you should have a result set that gets returned that contains the 'confirm_number' of the newly created entry. This should also, pretty much, eliminate any chance of a race condition. Since everything is happening within mysql, it should be very hard to end up with a condition that you start stomping on records. Let the list know if it works for you. -- Jim Lucas "Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them." Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
>Ok, so just that I am clear, you are SELECTing and pulling all the data >that you are submitting in the above INSERT statement from the DB >initially, >then you are only modifying the confirm_number value and then re- >submitting all the values, as they originally were, Well, actually when all is said and done, a new record will be created with new information (Name, phone, email, etc) and the confirm_number is the previous+1 This whole thing is a contact form. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, Nov 14, 2008 at 1:22 PM, <[EMAIL PROTECTED]> wrote:
> update contacts set confirm_number = confirm_number + 1 order by
>> contact
>>
>>> desc limit 1
>>>
>>
> Here is the php query I've been using to send the record in the first
> place
>
> $query="INSERT INTO contacts (first_name, last_name, email, phn_number,
> address, city, state, zip, dates, comments, confirm_number) VALUES
> ('$FirstName', '$LastName', '$Email', '$Phone', '$Address', '$City',
> '$selected_state', '$Zip', '$newdate', '$Comments' ,
> '$confirm_number' )";
> [EMAIL PROTECTED] ($query);
>
> (obviously in the script, it's all on one line)
>
> Now, what I need to do, is somehow pull make confirm_number get
> submitted as a new record, which will happen once they submit the form,
> but I want it to be submitted +1. (12345 should now be 12346 but a new
> record entirely)
>
>
> I was trying this code before the submission query:
>
> $confirmnumber="SELECT confirm_number from contacts ORDER BY contact
> DESC LIMIT 1";
> $confirm_number=$confirmnumber+1;
>
> Now what this is doing for me so far, is just taking the first numeral
> of the record, which happens to be 4 (I originally added the
> confirm_number via the rand function, but have since taken that out) and
> adding 1 to it and that is it.
>
> So the new $confirm_number=5
> So it sort of did the job, but not quite..
>
>
> The question is how can I make a new record (I know the answer to that
> part) BUT with a confirm_number of 1 greater than the previous record.
>
>
>
> --
> Michael S. Dunsavage
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
make that field an autonumber and let the database assign it. much much
cleaner and simple to get the number with mysql_last_insert()
--
Bastien
Cat, the other other white meat
Re: [PHP] mySQL query question
[EMAIL PROTECTED] wrote:
>> update contacts set confirm_number = confirm_number + 1 order by
>> contact
>>> desc limit 1
>
> Here is the php query I've been using to send the record in the first
> place
>
> $query="INSERT INTO contacts (first_name, last_name, email, phn_number,
> address, city, state, zip, dates, comments, confirm_number) VALUES
> ('$FirstName', '$LastName', '$Email', '$Phone', '$Address', '$City',
> '$selected_state', '$Zip', '$newdate', '$Comments' ,
> '$confirm_number' )";
> [EMAIL PROTECTED] ($query);
>
> (obviously in the script, it's all on one line)
>
> Now, what I need to do, is somehow pull make confirm_number get
> submitted as a new record, which will happen once they submit the form,
> but I want it to be submitted +1. (12345 should now be 12346 but a new
> record entirely)
>
>
> I was trying this code before the submission query:
>
> $confirmnumber="SELECT confirm_number from contacts ORDER BY contact
> DESC LIMIT 1";
> $confirm_number=$confirmnumber+1;
>
> Now what this is doing for me so far, is just taking the first numeral
> of the record, which happens to be 4 (I originally added the
> confirm_number via the rand function, but have since taken that out) and
> adding 1 to it and that is it.
>
> So the new $confirm_number=5
> So it sort of did the job, but not quite..
>
>
> The question is how can I make a new record (I know the answer to that
> part) BUT with a confirm_number of 1 greater than the previous record.
>
>
>
Ok, so just that I am clear, you are SELECTing and pulling all the data that
you are submitting in the above INSERT statement from the DB initially,
then you are only modifying the confirm_number value and then re-submitting all
the values, as they originally were, back to the DB and creating a
copy of the original data. The only difference being that the $confirm_number
has been altered. Correct?
--
Jim Lucas
"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
update contacts set confirm_number = confirm_number + 1 order by
contact
desc limit 1
Here is the php query I've been using to send the record in the first
place
$query="INSERT INTO contacts (first_name, last_name, email, phn_number,
address, city, state, zip, dates, comments, confirm_number) VALUES
('$FirstName', '$LastName', '$Email', '$Phone', '$Address', '$City',
'$selected_state', '$Zip', '$newdate', '$Comments' ,
'$confirm_number' )";
[EMAIL PROTECTED] ($query);
(obviously in the script, it's all on one line)
Now, what I need to do, is somehow pull make confirm_number get
submitted as a new record, which will happen once they submit the form,
but I want it to be submitted +1. (12345 should now be 12346 but a new
record entirely)
I was trying this code before the submission query:
$confirmnumber="SELECT confirm_number from contacts ORDER BY contact
DESC LIMIT 1";
$confirm_number=$confirmnumber+1;
Now what this is doing for me so far, is just taking the first numeral
of the record, which happens to be 4 (I originally added the
confirm_number via the rand function, but have since taken that out) and
adding 1 to it and that is it.
So the new $confirm_number=5
So it sort of did the job, but not quite..
The question is how can I make a new record (I know the answer to that
part) BUT with a confirm_number of 1 greater than the previous record.
--
Michael S. Dunsavage
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, Nov 14, 2008 at 9:58 AM, <[EMAIL PROTECTED]> wrote: > > > okay I want to pull an integer from a database called confirm_number, > > add 1 and repost it back to the database > > No, you don't want to do that. > :-) > > You are introducing a race condition between TWO users who hit the same > page at the same time. > > They each get, say, 42, and they each put back 43, but one of them should > have been 44. > > What you WANT to do is this: > update contacts set confirm_number = confirm_number + 1 order by contact > desc limit 1 > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > yep, our current app, designed by 'brighter minds' than mine, refused to make these auto numbers and now we have problems caused by the race condition. -- Bastien Cat, the other other white meat
[PHP] mySQL query question
> okay I want to pull an integer from a database called confirm_number, > add 1 and repost it back to the database No, you don't want to do that. :-) You are introducing a race condition between TWO users who hit the same page at the same time. They each get, say, 42, and they each put back 43, but one of them should have been 44. What you WANT to do is this: update contacts set confirm_number = confirm_number + 1 order by contact desc limit 1 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
I would create a separate table for this (confirmation_numbers or something) with an autoincrement primary key. That way you can simply insert a new record for you contact and then ask (using mysql_insert_id()) what the confirmation number is. This approach is much safer as you can be 100% sure the number is unique and it's much less complicated. (of course you still need to check if the query didn't fail) - http://devshed.excudo.net http://devshed.excudo.net -- View this message in context: http://www.nabble.com/mySQL-query-question-tp20495466p20501473.html Sent from the PHP - General mailing list archive at Nabble.com. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
okay I want to pull an integer from a database called confirm_number, add 1 and repost it back to the database here's the code I'm using. $queryconfirm="SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1"; I assume that you are already aware that if you set the variable $queryconfirm to this SQL query that the query is not necessarily executed and returns the result into the variable. $confirmresult=$queryconfirm; Now what you did is that $confirmresult is now: "SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1" as well as $queryconfirm. Why is that? now here's the problem I want to add 1 to confirm_number: $confirm_number=$confirmresult; $confirm_number+=1; I will also assume that you don't think that $confirm_number will magically contain the result. Now all this does is set the confirm_number record to 1 in the database. So I assume that $queryconfirm somehow is not being passed to confirm_number? But, while we're here the confirm_number is supposed to be 5 digits long and the +1 should make it 10001, 10002, 10003 etc how would I set the original number to 1000 to begin with? Or should I just set that in the database record its self when I'm ready to use the website? If what I assume is right (I don't want to disappoint you) but you should start reading some PHP basics on how you make database connection in PHP. Use google to find some tutorial for e.g. If I am wrong post us the code to see if someone can help. -- Thodoris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 08:46 +0100, Jochem Maas wrote: > 1000 + 1 != 10001 > > you might consider setting a default of 1000 or 1 or whatever on > the given > field so it's automatically populated with that number when a contact > record is > created. Sorry. Hit the 0 one to few times. -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
Michael S. Dunsavage schreef: > okay I want to pull an integer from a database called confirm_number, > add 1 and repost it back to the database > > > here's the code I'm using. > > > $queryconfirm="SELECT confirm_number from contacts ORDER BY contact DESC > LIMIT 1"; > $confirmresult=$queryconfirm; > > now here's the problem > > I want to add 1 to confirm_number: > > $confirm_number=$confirmresult; > $confirm_number+=1; > > Now all this does is set the confirm_number record to 1 in the database. > So I assume that $queryconfirm somehow is not being passed to AFAIKT your not querying the DB at all and your merely adding 1 to a string, which results in 1. adding 1 to a php variable will never cause and update in a database ... well actually there might be a way to do that but I can't think of it using some kind of hyper funky object but I'm pretty sure there is no way to overload the + operator. > confirm_number? But, while we're here the confirm_number is supposed to > be 5 digits long and the +1 should make it 10001, 10002, 10003 etc > > how would I set the original number to 1000 to begin with? Or should I > just set that in the database record its self when I'm ready to use the > website? 1000 + 1 != 10001 you might consider setting a default of 1000 or 1 or whatever on the given field so it's automatically populated with that number when a contact record is created. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 00:52 -0600, Micah Gersten wrote: > If you're just adding one, there is no reason to retrieve the data, > process it, and update it. You can just update the number. > http://dev.mysql.com/doc/refman/5.0/en/update.html But, the problem is that the confirm_number is a confirmation number that gets e-mailed out. So I have to pull it from the DB any way -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
If you're just adding one, there is no reason to retrieve the data, process it, and update it. You can just update the number. http://dev.mysql.com/doc/refman/5.0/en/update.html Also, you should read the MySQL manual on default values: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Michael S. Dunsavage wrote: > okay I want to pull an integer from a database called confirm_number, > add 1 and repost it back to the database > > > here's the code I'm using. > > > $queryconfirm="SELECT confirm_number from contacts ORDER BY contact DESC > LIMIT 1"; > $confirmresult=$queryconfirm; > > now here's the problem > > I want to add 1 to confirm_number: > > $confirm_number=$confirmresult; > $confirm_number+=1; > > Now all this does is set the confirm_number record to 1 in the database. > So I assume that $queryconfirm somehow is not being passed to > confirm_number? But, while we're here the confirm_number is supposed to > be 5 digits long and the +1 should make it 10001, 10002, 10003 etc > > how would I set the original number to 1000 to begin with? Or should I > just set that in the database record its self when I'm ready to use the > website? > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mySQL query question
okay I want to pull an integer from a database called confirm_number, add 1 and repost it back to the database here's the code I'm using. $queryconfirm="SELECT confirm_number from contacts ORDER BY contact DESC LIMIT 1"; $confirmresult=$queryconfirm; now here's the problem I want to add 1 to confirm_number: $confirm_number=$confirmresult; $confirm_number+=1; Now all this does is set the confirm_number record to 1 in the database. So I assume that $queryconfirm somehow is not being passed to confirm_number? But, while we're here the confirm_number is supposed to be 5 digits long and the +1 should make it 10001, 10002, 10003 etc how would I set the original number to 1000 to begin with? Or should I just set that in the database record its self when I'm ready to use the website? -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Sanjeev N wrote: > Hi Jim Lucas, > > You are correct... i want to run in the same way. > > but as my 2 tables, column name are different i cant run the LOAD DATA > infile. If you're inserting the same data, then use LOAD DATA INFILE to load it into a temporary table, then use INSERT SELECT's to put them into the other tables. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Hi Jim Lucas, You are correct... i want to run in the same way. but as my 2 tables, column name are different i cant run the LOAD DATA infile. And the example you mentioned for break at 100, also i thought to use in that way. but one of the column had the text type which we cant predict about the size. Thanks for the support from all of you. Now, I am inserting the rows one by one only On 5/2/08, Chris <[EMAIL PROTECTED]> wrote: > > Jim Lucas wrote: > > Waynn Lue wrote: > >> Wouldn't using LOAD DATA INFILE be better than writing your own script? > >> > > > > depends, does the data file match the table column for column? > > > Doesn't have to. > > http://dev.mysql.com/doc/refman/5.0/en/load-data.html > > By default, when no column list is provided at the end of the LOAD DATA > INFILE statement, input lines are expected to contain a field for each > table column. If you want to load only some of a table's columns, > specify a column list: > > LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...); > > > But load data infile requires extra mysql privileges. > > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > -- Regards, Sanjeev http://www.sanchanworld.com | http://webdirectory.sanchanworld.com - submit your site
Re: [PHP] mysql query and maximum characters in sql statement
Jim Lucas wrote: > Waynn Lue wrote: >> Wouldn't using LOAD DATA INFILE be better than writing your own script? >> > > depends, does the data file match the table column for column? Doesn't have to. http://dev.mysql.com/doc/refman/5.0/en/load-data.html By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list: LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...); But load data infile requires extra mysql privileges. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Waynn Lue wrote: Wouldn't using LOAD DATA INFILE be better than writing your own script? depends, does the data file match the table column for column? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Wouldn't using LOAD DATA INFILE be better than writing your own script?
On 5/1/08, Jim Lucas <[EMAIL PROTECTED]> wrote:
> Jim Lucas wrote:
> > Sanjeev N wrote:
> >> Hi,
> >> I have written a program which imports the tab delimited file and
> >> insert all
> >> the line from file to the mysql line by line.
> >> I am succeding in the above case. but problem with the above method is
> >> its
> >> taking to too much time while inserting into the database.
> >> The file's size will be more than 5000 lines.
> >>
> >> Then i tried to build a string of ; seperated queries. as follows
> >>
> >> for($i=1; $i >>$insert_string .= "insert into tablename (v1, v2. v6)
> >> values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');";
> >> }
> >> if(!empty($insert_string)){
> >> mysql_query($insert_string, $conn) or die("query failed :
> >> ".mysql_errror());
> >> }
> >>
> >> Its throwing error saying check the manual for right syntax.
> >>
> >> After investigating in some sites i come to know that its problem of
> >> limitations in query size.
> >>
> >> I also tried with "SET GLOBAL max_allowed_packet=3000;"
> >> Then also its throwing the same error.
> >>
> >> Can anybody tell me how to fix this error and reduce the inserting
> >> time with
> >> a single statement instead of writing more insert statements
> >>
> >
> > You are probably looking for something like this.
> >
> > >
> > if ( count($array) ) {
> > $insert_string = "INSERT INTO tablename (v1, v2. v6) VALUES ";
> > $data = array();
> > foreach ( $array AS $row ){
> > $row_clean = array_map('mysql_real_escape_string', $row);
> > $data[] = "('{$row_clean[1]}',
> > '{$row_clean[2]}',.'{$row_clean[6]}')";
> > }
> > $insert_string = join(', ', $data);
> > mysql_query($insert_string, $conn) or die("query failed :
> > ".mysql_errror());
> > } else {
> > echo "Nothing to insert";
> > }
> >
> > ?>
> >
>
> That would work, but will probably result in a query string that is too
> long.
>
> I'll redo the above to fix that.
>
>
>
> # How often do you want to insert??
> $break_at = 100;
>
> # Initialize the counter
> $cnt = 0;
>
> # Initial insert string
> $insert_string = "INSERT INTO tablename (v1, v2. v6) VALUES ";
>
> # if there is data, then process, otherwise skip it.
> if ( count($array) ) {
>
>$data = array();
>
># Loop through data
>foreach ( $array AS $row ) {
>
> $cnt++;
>
> # Clean the result data
> $row_clean = array_map('mysql_real_escape_string', $row);
>
> # Build data string and push it onto the data array.
> $data[] = "('{$row_clean[1]}',
> '{$row_clean[2]}',.'{$row_clean[6]}')";
>
> # Break and insert if we are at the break point
> if ( $cnt === $break_at ) {
>
># Reset Counter
>$cnt = 0;
>
># Run insert
>mysql_query($insert_string . join(', ', $data), $conn) or
> die("query failed : ".mysql_error());
>
># Reset data array
>$data = array();
>
> } //if
>
>} //foreach
>
># This should take care of any extra that didn't get processed in the
> foreach
>if ( count($data) ) {
>
># Insert remaining data
>mysql_query($insert_string . join(', ', $data), $conn) or
> die("query failed : ".mysql_error());
>
>} //if
>
> } else {
>
>echo "Nothing to insert";
>
> } //if
>
> ?>
>
>
> --
> Jim Lucas
>
> "Some men are born to greatness, some achieve greatness,
> and some have greatness thrust upon them."
>
> Twelfth Night, Act II, Scene V
> by William Shakespeare
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Jim Lucas wrote:
Sanjeev N wrote:
Hi,
I have written a program which imports the tab delimited file and
insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is
its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $imysql_query($insert_string, $conn) or die("query failed :
".mysql_errror());
}
Its throwing error saying check the manual for right syntax.
After investigating in some sites i come to know that its problem of
limitations in query size.
I also tried with "SET GLOBAL max_allowed_packet=3000;"
Then also its throwing the same error.
Can anybody tell me how to fix this error and reduce the inserting
time with
a single statement instead of writing more insert statements
You are probably looking for something like this.
$data[] = "('{$row_clean[1]}',
'{$row_clean[2]}',.'{$row_clean[6]}')";
}
$insert_string = join(', ', $data);
mysql_query($insert_string, $conn) or die("query failed :
".mysql_errror());
} else {
echo "Nothing to insert";
}
?>
That would work, but will probably result in a query string that is too long.
I'll redo the above to fix that.
--
Jim Lucas
"Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them."
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Sanjeev N wrote: Hi, I have written a program which imports the tab delimited file and insert all the line from file to the mysql line by line. I am succeding in the above case. but problem with the above method is its taking to too much time while inserting into the database. The file's size will be more than 5000 lines. Then i tried to build a string of ; seperated queries. as follows for($i=1; $i You are probably looking for something like this. -- Jim Lucas "Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them." Twelfth Night, Act II, Scene V by William Shakespeare -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query and maximum characters in sql statement
Sanjeev N <[EMAIL PROTECTED]> wrote:
> Hi,
> I have written a program which imports the tab delimited file and insert all
> the line from file to the mysql line by line.
> I am succeding in the above case. but problem with the above method is its
> taking to too much time while inserting into the database.
> The file's size will be more than 5000 lines.
>
> Then i tried to build a string of ; seperated queries. as follows
>
> for($i=1; $i$insert_string .= "insert into tablename (v1, v2. v6)
> values('$array[$i][1]', '$array[$i][2]'. '$array[$i][6]');";
> }
> if(!empty($insert_string)){
> mysql_query($insert_string, $conn) or die("query failed : ".mysql_errror());
> }
>
> Its throwing error saying check the manual for right syntax.
>
> After investigating in some sites i come to know that its problem of
> limitations in query size.
>
> I also tried with "SET GLOBAL max_allowed_packet=3000;"
> Then also its throwing the same error.
>
> Can anybody tell me how to fix this error and reduce the inserting time with
> a single statement instead of writing more insert statements
>
Sure, check with a MySQL list via http://www.mysql.com
Otherwise, split it up into multiple inserts.
I currently have a script which reads a 550,000 CSV file and uses
which all in all takes about 10 seconds to run the conversion and then the
inserts.
HTH,
Wolf
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql query and maximum characters in sql statement
Hi, I have written a program which imports the tab delimited file and insert all the line from file to the mysql line by line. I am succeding in the above case. but problem with the above method is its taking to too much time while inserting into the database. The file's size will be more than 5000 lines. Then i tried to build a string of ; seperated queries. as follows for($i=1; $ihttp://www.sanchanworld.com http://webdirectory.sanchanworld.com - submit your site
Re: [PHP] php - mysql query issue
On Fri, September 15, 2006 7:35 am, Dave Goodchild wrote: > Hi all. I am building an online events listing and when I run the > following > query I get the expected result set: > Any ideas why not? I know it's more of a mySQL question so apologies > in > advance! Well, you'd have to tell us what's in $start_string. Otherwise, we are just making wild guesses with nothing to back them up. And, really, to be 100% certain, you'd want to echo out the query you've built after $start_string is interpolated. $query = "SELECT ... '$start_string' ..."; echo $query, "\n"; mysql_query($query, $connection); After you've done that, it's dollars to donuts that you won't need us to answer the question. :-) -- 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] php - mysql query issue
Also you should check if dates.date is a DATE type, not DATETIME. Lost some time on that when I wanted to select enregs for a specific date, field was DATETIME and I was querying `date` = '2006-01-01'... :p Andy Dave Goodchild wrote: > On 15/09/06, Brad Bonkoski <[EMAIL PROTECTED]> wrote: >> >> Have you tried echoing out your query to run on the backend itself? >> Maybe there is some problem with how your join is being constructed... >> Perhaps a left outer join is called for? Hard to tell without having >> knowledge of your table structure and table data... >> >> Yep, I've echoed it and it says: > > "...WHERE dates.date = '2006-10-03'..." > > I think you're right on the join stakes, I will investigate further, many > thanks and have a great weekend. > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] php - mysql query issue
On 15/09/06, Brad Bonkoski <[EMAIL PROTECTED]> wrote: Have you tried echoing out your query to run on the backend itself? Maybe there is some problem with how your join is being constructed... Perhaps a left outer join is called for? Hard to tell without having knowledge of your table structure and table data... Yep, I've echoed it and it says: "...WHERE dates.date = '2006-10-03'..." I think you're right on the join stakes, I will investigate further, many thanks and have a great weekend.
Re: [PHP] php - mysql query issue
Have you tried echoing out your query to run on the backend itself? Maybe there is some problem with how your join is being constructed... Perhaps a left outer join is called for? Hard to tell without having knowledge of your table structure and table data... -B Dave Goodchild wrote: Hi all. I am building an online events listing and when I run the following query I get the expected result set: SELECT events.id AS eventid, name, postcode, start_time, dates.date FROM events, dates_events, dates WHERE dates_events.event_id = events.id and dates_events.date_id = dates.id AND dates.date >= '$start_string' AND dates.date <= '$end_string' ORDER BY date ASC ...however, when I look for a one-off event the following query fails: SELECT events.id AS eventid, name, postcode, start_time, dates.date FROM events, dates_events, dates WHERE dates_events.event_id = events.id and dates_events.date_id = dates.id AND dates.date = '$start_string' ORDER BY date ASC ...if I query for that date in the dates table using this: SELECT * FROM dates WHERE date = '$start_string' I get the date record I expect. The second query above cannot seem to look for a date that equals the supplied string (BTW, all data has been escaped prior to interpolation in the query string!) Any ideas why not? I know it's more of a mySQL question so apologies in advance! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] php - mysql query issue
Hi all. I am building an online events listing and when I run the following query I get the expected result set: SELECT events.id AS eventid, name, postcode, start_time, dates.date FROM events, dates_events, dates WHERE dates_events.event_id = events.id and dates_events.date_id = dates.id AND dates.date >= '$start_string' AND dates.date <= '$end_string' ORDER BY date ASC ...however, when I look for a one-off event the following query fails: SELECT events.id AS eventid, name, postcode, start_time, dates.date FROM events, dates_events, dates WHERE dates_events.event_id = events.id and dates_events.date_id = dates.id AND dates.date = '$start_string' ORDER BY date ASC ...if I query for that date in the dates table using this: SELECT * FROM dates WHERE date = '$start_string' I get the date record I expect. The second query above cannot seem to look for a date that equals the supplied string (BTW, all data has been escaped prior to interpolation in the query string!) Any ideas why not? I know it's more of a mySQL question so apologies in advance! -- http://www.web-buddha.co.uk http://www.projectkarma.co.uk
Re: [PHP] mysql query/$post problem
PHP Mailer wrote:
Mark skrev:
[snip]
$query = "INSERT INTO users AVATARS WHERE id =$user_id '','$avname')";
mysql_query($query);s
[snip]
I am trying to insert the value of $avname into the users table, into
the avatar field.
I think what you are trying to do is coordinated a bit wrong, perhaps
http://www.w3schools.com/sql/sql_insert.asp
could be of some help for you to achieve this in the future.
Taking a look at your query, i do see what you are trying to do, but the
structure is wrong.
$query = "INSERT INTO users (avatars) VALUES ('$avname')WHERE id
='$user_id')";
Also - it looks like an UPDATE might be more suitable for what you want,
given that you've got a WHERE clause tacked on the end. Google for a
good SQL tutorial; the PHP mailing list is not the place to learn SQL :)
Jasper
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query/$post problem
Mark skrev:
I havnt even tried this query but i know its wrong can anyone help!
***
**
I am trying to insert the value of $avname into the users table, into the
avatar field.
Hello Mark,
I think what you are trying to do is coordinated a bit wrong, perhaps
http://www.w3schools.com/sql/sql_insert.asp
could be of some help for you to achieve this in the future.
Taking a look at your query, i do see what you are trying to do, but the
structure is wrong.
$query = "INSERT INTO users (avatars) VALUES ('$avname')WHERE id ='$user_id')";
As Nicolas said, it is important that you understand your abilities to
debug these queries by outputting them through simple commands such as
echo or even the php-mysql function mysql_error(); give these a try
Let us know how it works out!
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query/$post problem
On 3/27/06, Mark <[EMAIL PROTECTED]> wrote:
> I havnt even tried this query but i know its wrong can anyone help!
>
>
> ***
> include("header.php");
> include("connect.php");
>
> $comp_id = $_SESSION['comp_id'];
> $user_id = $_SESSION['user_id'];
>
> // Grab variables and insert into database
>
> $avname = $_POST['avname'];
>
>
> $query = "INSERT INTO users AVATARS WHERE id =$user_id '','$avname')";
The format for insert queries is:
insert into table(field1, field2) values ('value1', 'value2')
or
insert into table set field1=value2, field2=value2 etc.
What exactly is the tablename? Also you don't need the ) on the end:
$query = "INSERT INTO tablename SET id =$user_id '','$avname'";
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query/$post problem
Mark a écrit :
I havnt even tried this query but i know its wrong can anyone help!
***
don't you need a session_start() somewhere ?
(or it's in the header.php or connect.php perhaps ?)
$comp_id = $_SESSION['comp_id'];
$user_id = $_SESSION['user_id'];
// Grab variables and insert into database
$avname = $_POST['avname'];
$query = "INSERT INTO users AVATARS WHERE id =$user_id '','$avname')";
mysql_query($query);s
mysql_close();
include("footer.html");
?>
**
I am trying to insert the value of $avname into the users table, into the
avatar field.
could you tell us a bit more about what's in the $avname ?
try an echo $query and run your query with an sql client to check if the
database accepts your request.
N F
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql query/$post problem
I havnt even tried this query but i know its wrong can anyone help! *** ** I am trying to insert the value of $avname into the users table, into the avatar field. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query
On Wednesday 14 September 2005 07:36 pm, Jesús Alain Rodríguez Santos wrote:
> I have a table colum in mysql with two fields: day and month. I
> would like to know if it's possible to make a query where I can
> determine if exist days before to a selected day, for example:
> if I have in my table:
> day 19 - month 05, I wish to know if there are previous days
> inserted at the 19, the days they are not inserted in the table,
> they are inserted according your selection, what I want to get is
> that every time that you insert a day, I want to check if there
> are days previous to the one already inserted in the table in the
> same month, in case that there are not them then they owe you
> to insert together with the one selected,
I haven't tried this, but the logic should work according to the manual:
You don't have to check, you can just insert all the data. If the row
already exists, the data will not be inserted. This assumes that you have a
constraint on the table which prevents duplicate values!
$day=19;
$month=5;
for($i=1;i<=$day;i++){
$result=mysql_query("INSERT INTO table (month,day) VALUES (5,$i)");
}
If you don't have a constraint, then you will have to loop over the data for
that month and insert the rows where they don't already exist.
> I wait they understand me what I want:
> I work php/mysql.
create table tableA (
day int,
month int
);
select * from tableA where month=5 and day < 19;
This will select everything from the 5th month and before the 19th day of
the
5th month.
Is that what you were going for?
>
> sorry for my english i'm cuban
> Thank you and excuse the nuisances
>
>
>
> --
> Este mensaje ha sido analizado por MailScanner
> en busca de virus y otros contenidos peligrosos,
> y se considera que está limpio.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query
On Wednesday 14 September 2005 07:36 pm, Jesús Alain Rodríguez Santos wrote: > I have a table colum in mysql with two fields: day and month. I > would like to know if it's possible to make a query where I can > determine if exist days before to a selected day, for example: > if I have in my table: > day 19 - month 05, I wish to know if there are previous days > inserted at the 19, the days they are not inserted in the table, > they are inserted according your selection, what I want to get is > that every time that you insert a day, I want to check if there > are days previous to the one already inserted in the table in the > same month, in case that there are not them then they owe you > to insert together with the one selected, > I wait they understand me what I want: > I work php/mysql. create table tableA ( day int, month int ); select * from tableA where month=5 and day < 19; This will select everything from the 5th month and before the 19th day of the 5th month. Is that what you were going for? > > sorry for my english i'm cuban > Thank you and excuse the nuisances > > > > -- > Este mensaje ha sido analizado por MailScanner > en busca de virus y otros contenidos peligrosos, > y se considera que está limpio. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql query
I have a table colum in mysql with two fields: day and month. I would like to know if it's possible to make a query where I can determine if exist days before to a selected day, for example: if I have in my table: day 19 - month 05, I wish to know if there are previous days inserted at the 19, the days they are not inserted in the table, they are inserted according your selection, what I want to get is that every time that you insert a day, I want to check if there are days previous to the one already inserted in the table in the same month, in case that there are not them then they owe you to insert together with the one selected, I wait they understand me what I want: I work php/mysql. sorry for my english i'm cuban Thank you and excuse the nuisances -- Este mensaje ha sido analizado por MailScanner en busca de virus y otros contenidos peligrosos, y se considera que está limpio. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query update two table in one?
Tyler Replogle schrieb:
Can you update two tables in one mysql query
i've got these two queries
$db->query("update `dbn_members_counters` set views =(views +1) where
id = '$this->id' ");
$db->query("update `dbn_members` set lastaction = $conf->site_time,
page = '$this->page' where id = '$this->id' ");
and i was wonder if i could get them into one because there are right
next two each other and i'm trying to lower my query count.
Yes, you can do that. BUT you need mySQL > 4.0 for this! Use a JOIN.
Have a look at: http://dev.mysql.com/doc/mysql/en/update.html
Best regards,
Christian
From: K Karthik <[EMAIL PROTECTED]>
To: php-general@lists.php.net
Subject: [PHP] php-help
Date: Thu, 10 Mar 2005 12:31:35 +0530
MIME-Version: 1.0
Received: from mc7-f40.hotmail.com ([65.54.253.47]) by
IMC3-S26.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); Wed, 9 Mar
2005 22:59:41 -0800
Received: from lists.php.net ([216.92.131.4]) by mc7-f40.hotmail.com
with Microsoft SMTPSVC(6.0.3790.211); Wed, 9 Mar 2005 22:59:41 -0800
Received: from ([216.92.131.4:25901] helo=lists.php.net)by
pb1.pair.com (ecelerity HEAD r(5124)) with SMTPid
E8/F4-53294-F40FF224 for <[EMAIL PROTECTED]>; Thu, 10 Mar 2005
01:59:30 -0500
Received: (qmail 17042 invoked by uid 1010); 10 Mar 2005 06:58:09 -
Received: (qmail 17024 invoked by uid 1010); 10 Mar 2005 06:58:09 -
X-Message-Info: JGTYoYF78jFevGptXBXjGwKBSvHljkD+bF1qvT/FEkQ=
Return-Path: <[EMAIL PROTECTED]>
X-Host-Fingerprint: 216.92.131.4 lists.php.net Mailing-List: contact
[EMAIL PROTECTED]; run by ezmlm
Precedence: bulk
list-help:
list-unsubscribe:
list-post:
Delivered-To: mailing list php-general@lists.php.net
Delivered-To: [EMAIL PROTECTED]
Delivered-To: [EMAIL PROTECTED]
X-Host-Fingerprint: 203.193.155.110 alps.manageengine.org Linux 2.4/2.6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3)
Gecko/20040913
X-Accept-Language: en-us, en
X-OriginalArrivalTime: 10 Mar 2005 06:59:41.0597 (UTC)
FILETIME=[BBA044D0:01C5253E]
sir,
i'll explain my problem.and if anyone could help me i'll be thankful.
i am displaying a content read from a file into a text area of a form.
when i make changes andretrieve back in the text area, i encounter a
problem..
i.e., when i enter text="please enter"
i have an outputas text= /" please enter/"
thanks,
kkarthik
--
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 query update two table in one?
Can you update two tables in one mysql query
i've got these two queries
$db->query("update `dbn_members_counters` set views =(views +1) where id =
'$this->id' ");
$db->query("update `dbn_members` set lastaction = $conf->site_time, page =
'$this->page' where id = '$this->id' ");
and i was wonder if i could get them into one because there are right next
two each other and i'm trying to lower my query count.
From: K Karthik <[EMAIL PROTECTED]>
To: php-general@lists.php.net
Subject: [PHP] php-help
Date: Thu, 10 Mar 2005 12:31:35 +0530
MIME-Version: 1.0
Received: from mc7-f40.hotmail.com ([65.54.253.47]) by IMC3-S26.hotmail.com
with Microsoft SMTPSVC(6.0.3790.211); Wed, 9 Mar 2005 22:59:41 -0800
Received: from lists.php.net ([216.92.131.4]) by mc7-f40.hotmail.com with
Microsoft SMTPSVC(6.0.3790.211); Wed, 9 Mar 2005 22:59:41 -0800
Received: from ([216.92.131.4:25901] helo=lists.php.net)by pb1.pair.com
(ecelerity HEAD r(5124)) with SMTPid E8/F4-53294-F40FF224 for
<[EMAIL PROTECTED]>; Thu, 10 Mar 2005 01:59:30 -0500
Received: (qmail 17042 invoked by uid 1010); 10 Mar 2005 06:58:09 -
Received: (qmail 17024 invoked by uid 1010); 10 Mar 2005 06:58:09 -
X-Message-Info: JGTYoYF78jFevGptXBXjGwKBSvHljkD+bF1qvT/FEkQ=
Return-Path: <[EMAIL PROTECTED]>
X-Host-Fingerprint: 216.92.131.4 lists.php.net Mailing-List: contact
[EMAIL PROTECTED]; run by ezmlm
Precedence: bulk
list-help:
list-unsubscribe:
list-post:
Delivered-To: mailing list php-general@lists.php.net
Delivered-To: [EMAIL PROTECTED]
Delivered-To: [EMAIL PROTECTED]
X-Host-Fingerprint: 203.193.155.110 alps.manageengine.org Linux 2.4/2.6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3)
Gecko/20040913
X-Accept-Language: en-us, en
X-OriginalArrivalTime: 10 Mar 2005 06:59:41.0597 (UTC)
FILETIME=[BBA044D0:01C5253E]
sir,
i'll explain my problem.and if anyone could help me i'll be thankful.
i am displaying a content read from a file into a text area of a form.
when i make changes andretrieve back in the text area, i encounter a
problem..
i.e., when i enter text="please enter"
i have an outputas text= /" please enter/"
thanks,
kkarthik
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query
Sebastian wrote: > Hello, > im working on an article system and looking to avoid running three > queries. > example, i have this query: > > SELECT id,title FROM articles WHERE id=$_GET[id] > > now say $_GET[id] = 5 > > I would like to get the previous id 4 and the next id 6 (if there is one) > so i can do something like: You really shouldn't rely on an auto_increment 'id' field for your prev/next article linking... Particularly as you might need to delete articles some day. Better to have an explicit "rank" you can set so you can organize your articles in the order you like. Or to have a "whatdate" time-stamp to organize them by the date they were published. Or... anything EXCEPT relying on consequtive auto_increment 'id's > << Previous Article [Title] > Next Article [Ttitle] >> > > i would assume this is impossible without running mulitple queries? just > thought i'd ask in case i am wrong. i am using mysql 4x I would just go ahead and run the 3 queries. If you have designed your database properly, they will not be that much more expensive than your single query, because it's a very simple query and you only run the 3 queries once on a page. Benchmark it and see -- You're probably worrying about micro-seconds when you shouldn't. -- 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 query
On Thu, 2005-02-17 at 22:02, Jason Petersen wrote: > On 17 Feb 2005 19:28:18 -0600, Bret Hughes <[EMAIL PROTECTED]> wrote: > > On Thu, 2005-02-17 at 18:24, Sebastian wrote: > > > Hello, > > > im working on an article system and looking to avoid running three > > > queries. > > > example, i have this query: > > > > > > SELECT id,title FROM articles WHERE id=$_GET[id] > > > > > > now say $_GET[id] = 5 > > > > > > I would like to get the previous id 4 and the next id 6 (if there is one) > > > so i can do something like: > > I would do something like: > > $theId = $_GET['id']; > $ids = $theId-1 . ", ". $theId ", ". $theId+1; > $query = "SELECT id, title FROM articles WHERE id IN ($ids)"; > That is a good approach I wonder if there is any difference in performance? ... To answer my own question before I even post it: A quick test or two shows that on a postgres table with about 45K rows both ways used an index scan. on a table with about 35 rows the < > always used a index scan but the in() used a seq scan. I am sure mysql would use the index both ways as well but I do not know. Seems like the smaller the number of elements used the better off you are with the in() deal since each element in the index had to be compared to each of the numbers where as with the <> approach only two comparisions have to be made regardless of the range. This is not to say my approach was better I am just thinking out loud. Bret -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query
On 17 Feb 2005 19:28:18 -0600, Bret Hughes <[EMAIL PROTECTED]> wrote: > On Thu, 2005-02-17 at 18:24, Sebastian wrote: > > Hello, > > im working on an article system and looking to avoid running three queries. > > example, i have this query: > > > > SELECT id,title FROM articles WHERE id=$_GET[id] > > > > now say $_GET[id] = 5 > > > > I would like to get the previous id 4 and the next id 6 (if there is one) > > so i can do something like: I would do something like: $theId = $_GET['id']; $ids = $theId-1 . ", ". $theId ", ". $theId+1; $query = "SELECT id, title FROM articles WHERE id IN ($ids)"; -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query
On Thu, 2005-02-17 at 18:24, Sebastian wrote: > Hello, > im working on an article system and looking to avoid running three queries. > example, i have this query: > > SELECT id,title FROM articles WHERE id=$_GET[id] > > now say $_GET[id] = 5 > > I would like to get the previous id 4 and the next id 6 (if there is one) > so i can do something like: > sure how about : select id, title from articles where id >= $_GET[id] - 1 and id <= $_GET[id] + 1 order by num; should be valid sql but I have no experience with mysql Bret -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query
Now I am assuming that 4 and 6 would not always be the next article ID -
otherwise you could just increment your id accordingly.
If you wanted to do that without running multiple queries, this is who I
might do it, rather sloppy, but I think it would work.
$idGet= $_GET['id'];
$sql = "select id, title from articles";
$db->query($sql);
$i=0;
while($db->getRow()) {
$id[$i] = $db->row['id'];
$title[$i] = $db->row['title'];
$i++;
if($id[$i] == $idGet) {
$j = $I;
}
}
$prev = $j-1;
$next = $j+1;
<>
http://www.thelonecoder.com
[EMAIL PROTECTED]
562.924.4454 (office)
562.924.4075 (fax)
continuing the struggle against bad code
*/
?>
> From: "Sebastian" <[EMAIL PROTECTED]>
> Date: Thu, 17 Feb 2005 19:24:50 -0500
> To:
> Subject: [PHP] mysql query
>
> Hello,
> im working on an article system and looking to avoid running three queries.
> example, i have this query:
>
> SELECT id,title FROM articles WHERE id=$_GET[id]
>
> now say $_GET[id] = 5
>
> I would like to get the previous id 4 and the next id 6 (if there is one)
> so i can do something like:
>
> << Previous Article [Title]
> Next Article [Ttitle] >>
>
> i would assume this is impossible without running mulitple queries? just
> thought i'd ask in case i am wrong. i am using mysql 4x
>
> thanks
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query
Sebastian wrote:
Hello,
im working on an article system and looking to avoid running three queries.
example, i have this query:
SELECT id,title FROM articles WHERE id=$_GET[id]
now say $_GET[id] = 5
I would like to get the previous id 4 and the next id 6 (if there is one)
so i can do something like:
<< Previous Article [Title]
Next Article [Ttitle] >>
i would assume this is impossible without running mulitple queries? just
thought i'd ask in case i am wrong. i am using mysql 4x
thanks
Is it possible to make a function that would increase/decrease a
"counter" which would contain the page number that they were on? I am
not sure about doing all this, since I am a bit new, but I believe
that's what I'd investigate.
$pagenumber = thepagenumberthatweareon
int getNextPage(int currentPage, int scale), where scale 'up' or 'down'
function getNextPage($currentPage, $scale) {
global $pagenumber;
if ($scale = 'up') {
return "$currentpage" + "1";
} else {
return "$currentpage" - "1";
}
}
That could be a totally bogus function, but I thought it'd be fun to
throw that out. It at least gives you something to go on, I guess.
As long as we're looking at this, anyone want to suggest to me if I had
done anything wrong with that function, and how I can improve on it?
Thanks
-dant
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] mysql query
Hello, im working on an article system and looking to avoid running three queries. example, i have this query: SELECT id,title FROM articles WHERE id=$_GET[id] now say $_GET[id] = 5 I would like to get the previous id 4 and the next id 6 (if there is one) so i can do something like: << Previous Article [Title] Next Article [Ttitle] >> i would assume this is impossible without running mulitple queries? just thought i'd ask in case i am wrong. i am using mysql 4x thanks -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MYSQL Query question
John Nichel wrote: Raditha Dissanayake wrote: Reinhart Viane wrote: And a last question: I always seem to get stuck on mysql queries when scripting. mysql.com gives me a headache whens earching something. Does someone know a good mysql manual site or a good mysql book? That does not mean mysql questions should be posted on php mailing lists. There you go again, trying to keep this list on topic. ;) Alright I am going to change. as they say if you can't beat them join them. obligatory off topic post: Do you think it's better to use innodb type tables or myisam type tables when using mysql even without foreign keys? -- Raditha Dissanayake. -- http://www.radinks.com/print/card-designer/ | Card Designer Applet http://www.radinks.com/upload/ | Drag and Drop Upload -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MYSQL Query question
Raditha Dissanayake wrote: Reinhart Viane wrote: And a last question: I always seem to get stuck on mysql queries when scripting. mysql.com gives me a headache whens earching something. Does someone know a good mysql manual site or a good mysql book? That does not mean mysql questions should be posted on php mailing lists. There you go again, trying to keep this list on topic. ;) -- John C. Nichel ÜberGeek KegWorks.com 716.856.9675 [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MYSQL Query question
First, since it was a combined question of php and mysql I thought of sending it here. Secondly, this is my standard footer. My appologizes if my question irritates you -Original Message- From: Raditha Dissanayake [mailto:[EMAIL PROTECTED] Sent: donderdag 9 december 2004 14:14 To: [EMAIL PROTECTED] Subject: Re: [PHP] MYSQL Query question >Reinhart Viane wrote: >And a last question: >I always seem to get stuck on mysql queries when scripting. mysql.com >gives me a headache whens earching something. Does someone know a good >mysql manual site or a good mysql book? > > >That does not mean mysql questions should be posted on php mailing lists. > > > >STRICTLY PERSONAL AND CONFIDENTIAL >This message may contain confidential and proprietary material for the >sole use of the intended >recipient. Any review or distribution by others is strictly prohibited. >If you are not the intended >recipient please contact the sender and delete all copies. > > >Do you know that mailing lists are automatically archived at thousands >of websites? > > > > -- Raditha Dissanayake. -- http://www.radinks.com/print/card-designer/ | Card Designer Applet http://www.radinks.com/upload/ | Drag and Drop Upload -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MYSQL Query question
Reinhart Viane wrote: And a last question: I always seem to get stuck on mysql queries when scripting. mysql.com gives me a headache whens earching something. Does someone know a good mysql manual site or a good mysql book? That does not mean mysql questions should be posted on php mailing lists. STRICTLY PERSONAL AND CONFIDENTIAL This message may contain confidential and proprietary material for the sole use of the intended recipient. Any review or distribution by others is strictly prohibited. If you are not the intended recipient please contact the sender and delete all copies. Do you know that mailing lists are automatically archived at thousands of websites? -- Raditha Dissanayake. -- http://www.radinks.com/print/card-designer/ | Card Designer Applet http://www.radinks.com/upload/ | Drag and Drop Upload -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MYSQL Query question
Table chat_online:
session (varchar)
activity (datetime)
Table persons
persons_region_int(int)
Table regions
region_id
region_name
On a page i list all persons which are in the chat_online dbase and
within a certain period:
$limit_time = time() - 130; // 2 Minutes time out. 60 * 2 = 120
$sqlchatonline = "SELECT * FROM chat_online WHERE
UNIX_TIMESTAMP(activity) >= $limit_time AND
(sessionid!='".session_id()."')";
$resultchatonline=mysql_query($sqlchatonline) or die (mysql_error());
$chatvisits = mysql_num_rows($resultchatonline);
while($rowchatonline = mysql_fetch_object($resultchatonline)){
$chattersessionid=$rowchatonline->sessionid;
//get the username, userid, mainpicid from the online chatter
$getinfo= "select * from persons where
person_session_id='$chattersessionid'";
$resultgetinfo = mysql_query($getinfo) or die (mysql_error());
$rowgetinfo= mysql_fetch_array($resultgetinfo);
echo $rowgetinfo['person_nick'];
}
Now i want these online chatters to be listed by person_region_int:
something like:
region A
chatter1
chatter2
region B
none
region C
chatter3
chatter4
How do i do this?
And a second question:
I have created a menu box which lists all regions, if a option is
selected by the user, i only want to show the online chatters of the
selected region (selecting an option defines a variable $region which
holds the region_id)
Something like:
if ($region) {
$sqlchatonline = "SELECT * FROM chat_online, persons WHERE
UNIX_TIMESTAMP(chat_online.activity) >= $limit_time AND
(chat_online.sessionid!='".session_id()."' AND
(persons.persons_region_int='$region')";
}
This doe not give me the correct result: it shows all online chatters *
total amount of users of that region. It should be all online chatters
from that specified region
And a last question:
I always seem to get stuck on mysql queries when scripting. mysql.com
gives me a headache whens earching something. Does someone know a good
mysql manual site or a good mysql book?
Thx in advance
Reinhart
_
Reinhart Viane
[EMAIL PROTECTED]
Domos || D-Studio
Graaf Van Egmontstraat 15/3 -- B 2800 Mechelen -- tel +32 15 44 89 01 --
fax +32 15 43 25 26
STRICTLY PERSONAL AND CONFIDENTIAL
This message may contain confidential and proprietary material for the
sole use of the intended
recipient. Any review or distribution by others is strictly prohibited.
If you are not the intended
recipient please contact the sender and delete all copies.
RE: [PHP] mysql query with exclude
Little correction:
So something like:
select * from chat c1,
chat_online c2
where
UNIX_TIMESTAMP(c2.activity)>=$limit_time and
c2.session_id = (c2.user2_sessionid if (c1.user1_sessionid =
$thisuser)) or (c2.user1_sessionid if(c1.user2_sessionid = $thisuser));
I dunno if this is good sql, but I don't think it is.
>>Well
>>That was indeed what I was searching for but. If I read it out loud, I
think with this query I only check if the current user is still onlien
and not his conversation partner.
>>In the chat table is store the session_id's of both the chatters of
the conversation. There is no way to tell if $thisuser is the
user1_sessionid record or user2_sessionid record. So I check them both.
>>After that I have all results where (user1_sessionid record =
$thisuser or user2_sessionid record = $thisuser) I need to get the other
field. In the first case the users of which the time needs to be
>>>checked is user2_sessionid, in the second case user1_sessionid.
>>So something like:
>>select * from chat c1,
>> chat_online c2
>>where
>> UNIX_TIMESTAMP(c2.activity)>=$limit_time and
>> c2.session_id = (c2.user1_sessionid if (c1.user1_sessionid =
>>$thisuser)) or (c2.user1_sessionid if(c1.user2_sessionid =
$thisuser));
>>Can I do something like this?
>>-Original Message-
>>From: Ligaya Turmelle [mailto:[EMAIL PROTECTED]
>>Sent: zondag 28 november 2004 2:25
>>To: [EMAIL PROTECTED]
>>Cc: [EMAIL PROTECTED]
>>Subject: Re: [PHP] mysql query with exclude
>>Sounds like you need a join. Maybe something like:
>>
>>select * from chat c1,
>> chat_online c2
>>where
>> UNIX_TIMESTAMP(c2.activity)=$limit_time and
>> c2.session_id = $thisuser and
>> ((c1.user1_sessionid = $thisuser) or
>> c1.user2_sessionid = $thisuser));
>>Respectfully,
>>Ligaya Turmelle
>>Reinhart Viane wrote:
> Hey all,
>
> Hope you all have fun this saturday evening :)
> I'm sure i'm having fun except i'm kinda stuck...
>
> Ok here goes...
>
> I have 2 tables, one with the people online (chat_online): session_id
> activity
>
>
> And a second one where i keep the conversations between people(chat):
> user1_sessionid user2_sessionid
> chat_conv
>
> To see what chatter are still online during the last 2 minutes i do a
> check like this on the chat_online table: $limit_time= time()-130;
> $sqlchatonline="select * from chat_online where
UNIX_TIMESTAMP(activity)
>
>>=$limit_time";
>
>
> ok, on my page i also do a query to see what conversations are going
> on with the user: $thisuser=session_id();
> $getchatlist="select * from chat where (user1_sessionid=$thisuser) or
> (user2_sessionid=$thisuser)";
>
> This selects all the conversations which this user has been/or is
> into. I list all the chatpartners of thisuser. Off course it is
> possible that other chatters who had a conversation with this user are
> not online anymore. So i need to combine those two queries in a way...
>
> this is what i think it should be:
> $getchatlist=select * from chat where (user1_sessionid=$thisuser) or
> (user2_sessionid=$thisuser);
> $resultchatlist=mysql_query($getchatlist);
> while ($row=mysql_fetch_array($resultchatlist)) {
> get the second chattersessionid in each conversation and check
> if this chatter was still online in the last two minutes.
> if he is not, exclude him from the array and do not show him
> in the list (optional delete the record in the database) }
>
> or maybe i can combine those two queries in one?
>
> Can someone help me out on this?
>
> Thx in advance,
> Reinhart
>
>
>
>
>
> _
>
> Reinhart Viane
> <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
> Domos || D-Studio
> Graaf Van Egmontstraat 15/3 -- B 2800 Mechelen -- tel +32 15 44 89 01
--
> fax +32 15 43 25 26
>
>
> STRICTLY PERSONAL AND CONFIDENTIAL
> This message may contain confidential and proprietary material for the
> sole use of the intended
> recipient. Any review or distribution by others is strictly
prohibited.
> If you are not the intended
> recipient please contact the sender and delete all copies.
>
>
>
--
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
