Re: [PHP] mySQL query assistance...

[Daniel P. Brown]

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...

[Daniel P. Brown]

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

Re: [PHP] MySQL Query Help

[Simcha Younger]

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

[Ben Miller]

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

[PW]

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

[Simcha Younger]

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

[admin]

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

[Richard West]

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

Re: [PHP] MySQL Query Puzzle

[Shreyas Agasthya]

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

[Jim Lucas]

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

[Shreyas Agasthya]

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

[Shreyas Agasthya]

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

[Richard Quadling]

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

[Richard Quadling]

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

[Peter]

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

[shiplu]

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

[Shafiq Rehman]

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

[Paul M Foster]

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

Re: [PHP] MySQL query not working!

[tedd]

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!

[Andrew Ballard]

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!

[Midhun Girish]

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!

[Andrew Ballard]

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!

[Midhun Girish]

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!

[Ashley Sheridan]

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!

[Alexey Bovanenko]

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!

[Parham Doustdar]

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!

[Andre Polykanine]

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!

[Ashley Sheridan]

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] Re: php/mysql Query Question.

[Peter Ford]

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] mySQL query question

[Jim Lucas]

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

[Chris]

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

[Jim Lucas]

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

[Michael S. Dunsavage]

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]

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

[Jim Lucas]

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

[Michael S. Dunsavage]

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]

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

[Jim Lucas]

[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

[mikesd1]

>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

[Bastien Koert]

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

[Jim Lucas]

[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

[mikesd1]

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

[Bastien Koert]

On Fri, Nov 14, 2008 at 9:58 AM, <[EMAIL PROTECTED]> wrote:

>
> > okay I want to pull an integer from a database called confirm_number,
> > add 1 and repost it back to the database
>
> No, you don't want to do that.
> :-)
>
> You are introducing a race condition between TWO users who hit the same
> page at the same time.
>
> They each get, say, 42, and they each put back 43, but one of them should
> have been 44.
>
> What you WANT to do is this:
> update contacts set confirm_number = confirm_number + 1 order by contact
> desc limit 1
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
yep, our current app, designed by 'brighter minds' than mine, refused to
make these auto numbers and now we have problems caused by the race
condition.

-- 

Bastien

Cat, the other other white meat

Re: [PHP] mySQL query question

[Martijn Korse]

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

[Thodoris]

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

[Michael S. Dunsavage]

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

[Jochem Maas]

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

[Michael S. Dunsavage]

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

[Micah Gersten]

If you're just adding one, there is no reason to retrieve the data,
process it, and update it.  You can just update the number.
http://dev.mysql.com/doc/refman/5.0/en/update.html
Also, you should read the MySQL manual on default values:
http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



Michael S. Dunsavage wrote:
> okay I want to pull an integer from a database called confirm_number,
> add 1 and repost it back to the database
>
>
> here's the code I'm using.
>
>
> $queryconfirm="SELECT confirm_number from contacts ORDER BY contact DESC
> LIMIT 1";
> $confirmresult=$queryconfirm;
>
> now here's the problem 
>
> I want to add 1 to confirm_number:
>
> $confirm_number=$confirmresult;
> $confirm_number+=1;
>
> Now all this does is set the confirm_number record to 1 in the database.
> So I assume that $queryconfirm somehow is not being passed to
> confirm_number?  But, while we're here the confirm_number is supposed to
> be 5 digits long and the +1 should make it 10001, 10002, 10003 etc
>
> how would I set the original number to 1000 to begin with? Or should I
> just set that in the database record its self when I'm ready to use the
> website?
>   

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

Re: [PHP] mysql query and maximum characters in sql statement

[Chris]

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

[Sanjeev N]

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

[Chris]

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

[Jim Lucas]

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

[Waynn Lue]

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]

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

[Jim Lucas]

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

[Wolf]

 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

Re: [PHP] mysql query/$post problem

[Jasper Bryant-Greene]

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

[PHP Mailer]

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

[chris smith]

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

[nicolas figaro]

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

Re: [PHP] mysql query

[Mark Rees]

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

[Stephen Leaf]

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

Re: [PHP] mysql query update two table in one?

[Christian Heinrich]

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

Re: [PHP] mysql query

[Richard Lynch]

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

[Bret Hughes]

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

[Jason Petersen]

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

[Bret Hughes]

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

[Stephen Johnson]

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

[dan]

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

Re: [PHP] MYSQL Query question

[Raditha Dissanayake]

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

[John Nichel]

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

[Reinhart Viane]

First, since it was a combined question of php and mysql I thought of
sending it here.
Secondly, this is my standard footer.

My appologizes if my question irritates you


-Original Message-
From: Raditha Dissanayake [mailto:[EMAIL PROTECTED] 
Sent: donderdag 9 december 2004 14:14
To: [EMAIL PROTECTED]
Subject: Re: [PHP] MYSQL Query question


>Reinhart Viane wrote:

>And a last question:
>I always seem to get stuck on mysql queries when scripting. mysql.com 
>gives me a headache whens earching something. Does someone know a good 
>mysql manual site or a good mysql book?
>  
>
>That does not mean mysql questions should be posted on php mailing
lists.

> 
>
>
>STRICTLY PERSONAL AND CONFIDENTIAL
>This message may contain confidential and proprietary material for the
>sole use of the intended 
>recipient.  Any review or distribution by others is strictly
prohibited.
>If you are not the intended 
>recipient please contact the sender and delete all copies.
>  
>
>Do you know that mailing lists are automatically archived at thousands 
>of websites?

> 
>
>  
>


-- 
Raditha Dissanayake.
--
http://www.radinks.com/print/card-designer/ | Card Designer Applet
http://www.radinks.com/upload/  | Drag and Drop Upload 

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

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

Re: [PHP] MYSQL Query question

[Raditha Dissanayake]

Reinhart Viane wrote:
And a last question:
I always seem to get stuck on mysql queries when scripting. mysql.com
gives me a headache whens earching something. Does someone know a good
mysql manual site or a good mysql book?
 

That does not mean mysql questions should be posted on php mailing lists.

STRICTLY PERSONAL AND CONFIDENTIAL 
This message may contain confidential and proprietary material for the
sole use of the intended 
recipient.  Any review or distribution by others is strictly prohibited.
If you are not the intended 
recipient please contact the sender and delete all copies.
 

Do you know that mailing lists are automatically archived at thousands 
of websites?


 


--
Raditha Dissanayake.
--
http://www.radinks.com/print/card-designer/ | Card Designer Applet
http://www.radinks.com/upload/  | Drag and Drop Upload 

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

RE: [PHP] mysql query with exclude

[Reinhart Viane]

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

RE: [PHP] mysql query with exclude

[Reinhart Viane]

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

Re: [PHP] mysql query with exclude

[Ligaya Turmelle]

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 
  [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

RE: [PHP] MySQL query for average records per day OT

[Jay Blanchard]

[snip]
mysql> desc dates;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| the_date | date| YES  | | NULL|   |
| number   | int(11) |  | | 0   |   |
+--+-+--+-+-+---+
2 rows in set (0.00 sec)

mysql> select the_date, count(number), avg(number) from dates group by
the_date;
++---+-+
| the_date   | count(number) | avg(number) |
++---+-+
| 2004-01-01 | 3 |  2. |
| 2004-01-02 | 4 |  2.5000 |
| 2004-01-03 | 4 |  3.5000 |
++---+-+
3 rows in set (0.01 sec)
[/snip]

But the OP didn't have a 'number', just a date so...

mysql> select BilledFlag, count(BilledFlag), avg(BilledFlag) from
tblClassOthers group by BilledFlag;
++---+-+
| BilledFlag | count(BilledFlag) | avg(BilledFlag) |
++---+-+
| 2003-10-07 |   251 |2003 |
| 2003-10-14 |   174 |2003 |
| 2003-10-23 |   241 |2003 |
++---+-+

...fails. (count is correct, avg blows up) Without a number column what
is one to do?

mysql> SELECT (COUNT(BilledFlag)/COUNT(DISTINCT(BilledFlag))) AS average
FROM tblClassOthers;
+-+
| average |
+-+
|  222.00 |
+-+

Only gives the average. Adding any other count to this, including a
SUM(IF(BilledFlag <> '', 1, 0)) [as in a crosstab] causes the
(COUNT(BilledFlag)/COUNT(DISTINCT(BilledFlag))) calculation to fail,
returning only the currect COUNT

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

Re: [PHP] MySQL query for average records per day

[John Holmes]

From: "Greg Donald" <[EMAIL PROTECTED]>
mysql> select the_date, count(number), avg(number) from dates group by 
the_date;
++---+-+
| the_date   | count(number) | avg(number) |
++---+-+
| 2004-01-01 | 3 |  2. |
| 2004-01-02 | 4 |  2.5000 |
| 2004-01-03 | 4 |  3.5000 |
++---+-+
3 rows in set (0.01 sec)
I say again, I took the request as 1) Number of records per day (which you 
show, but can ignore the "number" column) and 2) The _overall_ average of 
those counts, which would be (3+4+4 records) / (3 days) = 3.666 records/day

We're arguing the same point, really, and trying to guess what the OP was 
after...

---John Holmes... 

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

Re: [PHP] MySQL query for average records per day

[Jeff Oien]

John Holmes wrote:
From: "Greg Donald" <[EMAIL PROTECTED]>
You can't get the average and a count in the same query

Sure you can.
mysql> select count(number), avg(number) from numbers;

Depends how you interpret his request, I guess. I took it as a request 
for the count of records per day and then the average of those counts.

So, if you had
D1
D1
D1
D1
D2
D2
D3
The count would be
D1 - 4
D2 - 2
D3 - 1
and the average would be (4+2+1)/3 = 2.333. Can you get that in one query?
depends what the OP was after, though...
---John Holmes...
Jay Blanchard's worked perfectly:
"SELECT (COUNT(date1)/COUNT(DISTINCT(date1))) AS average
FROM $table_name where date1 != '$today'";
I was looking for the average amount of records (submissions) per day.
Thanks.
Jeff
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] MySQL query for average records per day

[Greg Donald]

On Thu, 16 Sep 2004 16:08:45 -0400, John Holmes
<[EMAIL PROTECTED]> wrote:
> Depends how you interpret his request, I guess. I took it as a request for
> the count of records per day and then the average of those counts.
> 
> So, if you had
> 
> D1
> D1
> D1
> D1
> D2
> D2
> D3
> 
> The count would be
> 
> D1 - 4
> D2 - 2
> D3 - 1
> 
> and the average would be (4+2+1)/3 = 2.333. Can you get that in one query?

mysql> desc dates;
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| the_date | date| YES  | | NULL|   |
| number   | int(11) |  | | 0   |   |
+--+-+--+-+-+---+
2 rows in set (0.00 sec)

mysql> select * from dates;
+++
| the_date   | number |
+++
| 2004-01-01 |  1 |
| 2004-01-01 |  2 |
| 2004-01-01 |  3 |
| 2004-01-02 |  1 |
| 2004-01-02 |  2 |
| 2004-01-02 |  3 |
| 2004-01-02 |  4 |
| 2004-01-03 |  2 |
| 2004-01-03 |  3 |
| 2004-01-03 |  4 |
| 2004-01-03 |  5 |
+++
11 rows in set (0.00 sec)

mysql> select the_date, count(number), avg(number) from dates group by the_date;
++---+-+
| the_date   | count(number) | avg(number) |
++---+-+
| 2004-01-01 | 3 |  2. |
| 2004-01-02 | 4 |  2.5000 |
| 2004-01-03 | 4 |  3.5000 |
++---+-+
3 rows in set (0.01 sec)


-- 
Greg Donald
http://gdconsultants.com/
http://destiney.com/

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

Re: [PHP] MySQL query for average records per day

[John Holmes]

From: "Greg Donald" <[EMAIL PROTECTED]>
You can't get the average and a count in the same query
Sure you can.
mysql> select count(number), avg(number) from numbers;
Depends how you interpret his request, I guess. I took it as a request for 
the count of records per day and then the average of those counts.

So, if you had
D1
D1
D1
D1
D2
D2
D3
The count would be
D1 - 4
D2 - 2
D3 - 1
and the average would be (4+2+1)/3 = 2.333. Can you get that in one query?
depends what the OP was after, though...
---John Holmes... 

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

RE: [PHP] MySQL query for average records per day OT

[Jay Blanchard]

[snip]
> You can't get the average and a count in the same query

Sure you can.

mysql> select count(number), avg(number) from numbers;
+---+-+
| count(number) | avg(number) |
+---+-+
|18 |  2.3889 |
+---+-+
1 row in set (0.00 sec)
[/snip]

If it is numbers you can, but the OP wanted to average for dates.

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

Re: [PHP] MySQL query for average records per day

[Greg Donald]

On Thu, 16 Sep 2004 15:15:01 -0400, John Holmes
<[EMAIL PROTECTED]> wrote:
> You can't get the average and a count in the same query

Sure you can.

> desc numbers;
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| number | int(11) |  | | 0   |   |
++-+--+-+-+---+
1 row in set (0.00 sec)

mysql> select * from numbers;
++
| number |
++
|  1 |
|  2 |
|  1 |
|  2 |
|  1 |
|  2 |
|  1 |
|  2 |
|  1 |
|  2 |
|  3 |
|  4 |
|  3 |
|  4 |
|  3 |
|  4 |
|  3 |
|  4 |
++
18 rows in set (0.00 sec)

mysql> select count(number), avg(number) from numbers;
+---+-+
| count(number) | avg(number) |
+---+-+
|18 |  2.3889 |
+---+-+
1 row in set (0.00 sec)



-- 
Greg Donald
http://gdconsultants.com/
http://destiney.com/

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

RE: [PHP] MySQL query for average records per day

[Jay Blanchard]

[snip]
I have a database with a date field in this format
20041016
I'd like to count how many records were made on each day (except today) 
and average them. Can I do this in one query or will I need to do some 
more PHP stuff after I get some results? Thanks.

Bare bones so far:
$sql = "select date1 from $table_name where date1 != '$today'";
[/snip]

a. Is a SQL question
2. One query - "SELECT (COUNT(date1)/COUNT(DISTINCT(date1))) AS average
FROM $table_name where date1 != '$today'";

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

Re: [PHP] MySQL query for average records per day

[John Holmes]

From: "Jeff Oien" <[EMAIL PROTECTED]>
I have a database with a date field in this format
20041016
I'd like to count how many records were made on each day (except today) 
and average them. Can I do this in one query or will I need to do some 
more PHP stuff after I get some results? Thanks.

Bare bones so far:
$sql = "select date1 from $table_name where date1 != '$today'";
Jeff
$sql = "SELECT date1, COUNT(date1) AS num_records FROM $table_name WHERE 
date1 != CURRENT_DATE GROUP BY date1";

You can't get the average and a count in the same query, so either calculate 
the average in PHP as you extract the results or run another query.

$sql = "SELECT AVG(COUNT(date1)) AS avg_records FROM $table_name WHERE date1 
!= CURRENT_DATE GROUP BY date1";

Not sure if that second query is correct or not, though, since this is a PHP 
list.

---John Holmes... 

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

RE: [PHP] MySQL query for average records per day

[Gryffyn, Trevor]

Select date1,count(date1) group by date1 where date1 <> '$today'

That'll get you the count of how many records were done on each day
(excluding today).  I don't know what you're trying to average, but you
can probably figure it out from there.

-TG

> -Original Message-
> From: Jeff Oien [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, September 16, 2004 2:35 PM
> To: PHP
> Subject: [PHP] MySQL query for average records per day
> 
> 
> I have a database with a date field in this format
> 20041016
> I'd like to count how many records were made on each day 
> (except today) 
> and average them. Can I do this in one query or will I need 
> to do some 
> more PHP stuff after I get some results? Thanks.
> 
> Bare bones so far:
> $sql = "select date1 from $table_name where date1 != '$today'";
> Jeff
> 
> -- 
> 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 Results Not Appearing[Scanned]

[Michael Egan]

Glad to be of help. I'm sure there will be many more occasions when I'm badly in need 
of assistance.

Cheers,

Michael

-Original Message-
From: Harlequin [mailto:[EMAIL PROTECTED]
Sent: 02 July 2004 13:55
To: [EMAIL PROTECTED]
Subject: Re: [PHP] MySQL QUERY Results Not Appearing[Scanned]


Michael

just wanted to thank you through the group for your time.

U R A Star...!

--
-
 Michael Mason
 Arras People
 www.arraspeople.co.uk
-
"Michael Egan" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
Have a look at the mysql functions in the PHP manual - i.e.
http://uk.php.net/mysql_query

I'm also certain this question has been raised fairly recently on the list.
All mysql_query is going to do is either open up the resource or not. You'll
need to use something else to display the data.

For example:

   $sql = "select * from members";

   if(!$result = mysql_query($sql))
  echo mysql_error();

   while($row = mysql_fetch_array($result))
   {
  echo $row['column_name'];
   }


HTH,

Michael Egan

-Original Message-
From: Harlequin [mailto:[EMAIL PROTECTED]
Sent: 30 June 2004 12:42
To: [EMAIL PROTECTED]
Subject: [PHP] MySQL QUERY Results Not Appearing[Scanned]


Hi all...

Despite being able to make a connection to the server and execute a query I
am unable to do this through PHP using the following commands:

 echo "active members should appear here";
 $query = "SELECT * FROM RegisteredMembers";
 $result = mysql_query($query) or die ("could not execute query");
 echo "$result";

Any ideas...?

--
-
 Michael Mason
 Arras People
 www.arraspeople.co.uk
-

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
The information contained in this email (and in any attachments sent with
it) is confidential. It is intended for the addressee only. Access to this
email by anyone else is unintended and unauthorized.
If you are not the original addressee, 3tc asks you to please maintain
confidentiality. If you have received this email in error please notify 3tc
immediately by replying to it, then destroy any copies and delete it from
your computer system.
Any use, dissemination, forwarding, printing or copying of this email by
anyone except the addressee in the normal course of his/her business, is
strictly prohibited. 3tc owns the copyright in this email and any document
created by us and assert the right to be identified as the author of it.
Copyright has not been transferred to the addressee.
We protect our systems with Sophos Anti-virus -
www.sophos.com

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php 
  
  
The information contained in this email (and in any attachments sent with it) is 
confidential. It is intended for the addressee only. Access to this email by anyone 
else is unintended and unauthorized.  
If you are not the original addressee, 3tc asks you to please maintain 
confidentiality. If you have received this email in error please notify 3tc 
immediately by replying to it, then destroy any copies and delete it from your 
computer system. 
Any use, dissemination, forwarding, printing or copying of this email by anyone except 
the addressee in the normal course of his/her business, is strictly prohibited. 3tc 
owns the copyright in this email and any document created by us and assert the right 
to be identified as the author of it. Copyright has not been transferred to the 
addressee. 
We protect our systems with Sophos Anti-virus -  
www.sophos.com 
 

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

Re: [PHP] MySQL QUERY Results Not Appearing[Scanned]

[Harlequin]

Michael

just wanted to thank you through the group for your time.

U R A Star...!

--
-
 Michael Mason
 Arras People
 www.arraspeople.co.uk
-
"Michael Egan" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
Have a look at the mysql functions in the PHP manual - i.e.
http://uk.php.net/mysql_query

I'm also certain this question has been raised fairly recently on the list.
All mysql_query is going to do is either open up the resource or not. You'll
need to use something else to display the data.

For example:

   $sql = "select * from members";

   if(!$result = mysql_query($sql))
  echo mysql_error();

   while($row = mysql_fetch_array($result))
   {
  echo $row['column_name'];
   }


HTH,

Michael Egan

-Original Message-
From: Harlequin [mailto:[EMAIL PROTECTED]
Sent: 30 June 2004 12:42
To: [EMAIL PROTECTED]
Subject: [PHP] MySQL QUERY Results Not Appearing[Scanned]


Hi all...

Despite being able to make a connection to the server and execute a query I
am unable to do this through PHP using the following commands:

 echo "active members should appear here";
 $query = "SELECT * FROM RegisteredMembers";
 $result = mysql_query($query) or die ("could not execute query");
 echo "$result";

Any ideas...?

--
-
 Michael Mason
 Arras People
 www.arraspeople.co.uk
-

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
The information contained in this email (and in any attachments sent with
it) is confidential. It is intended for the addressee only. Access to this
email by anyone else is unintended and unauthorized.
If you are not the original addressee, 3tc asks you to please maintain
confidentiality. If you have received this email in error please notify 3tc
immediately by replying to it, then destroy any copies and delete it from
your computer system.
Any use, dissemination, forwarding, printing or copying of this email by
anyone except the addressee in the normal course of his/her business, is
strictly prohibited. 3tc owns the copyright in this email and any document
created by us and assert the right to be identified as the author of it.
Copyright has not been transferred to the addressee.
We protect our systems with Sophos Anti-virus -
www.sophos.com

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

RE: [PHP] MySQL QUERY Results Not Appearing[Scanned]

[Michael Egan]

Have a look at the mysql functions in the PHP manual - i.e. 
http://uk.php.net/mysql_query

I'm also certain this question has been raised fairly recently on the list. All 
mysql_query is going to do is either open up the resource or not. You'll need to use 
something else to display the data.

For example:

   $sql = "select * from members";

   if(!$result = mysql_query($sql))
  echo mysql_error();

   while($row = mysql_fetch_array($result))
   {
  echo $row['column_name'];
   }


HTH,

Michael Egan

-Original Message-
From: Harlequin [mailto:[EMAIL PROTECTED]
Sent: 30 June 2004 12:42
To: [EMAIL PROTECTED]
Subject: [PHP] MySQL QUERY Results Not Appearing[Scanned]


Hi all...

Despite being able to make a connection to the server and execute a query I
am unable to do this through PHP using the following commands:

 echo "active members should appear here";
 $query = "SELECT * FROM RegisteredMembers";
 $result = mysql_query($query) or die ("could not execute query");
 echo "$result";

Any ideas...?

-- 
-
 Michael Mason
 Arras People
 www.arraspeople.co.uk
-

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php 
The information contained in this email (and in any attachments sent with it) is 
confidential. It is intended for the addressee only. Access to this email by anyone 
else is unintended and unauthorized.  
If you are not the original addressee, 3tc asks you to please maintain 
confidentiality. If you have received this email in error please notify 3tc 
immediately by replying to it, then destroy any copies and delete it from your 
computer system. 
Any use, dissemination, forwarding, printing or copying of this email by anyone except 
the addressee in the normal course of his/her business, is strictly prohibited. 3tc 
owns the copyright in this email and any document created by us and assert the right 
to be identified as the author of it. Copyright has not been transferred to the 
addressee. 
We protect our systems with Sophos Anti-virus -  
www.sophos.com 
 

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

Re: [PHP] MYSQL Query results in pages

[Geethanandh Kandasamy]

No other way. Use one query to count the total record and then another
query for limiting

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

Re: [PHP] MYSQL Query results in pages

[Matt Matijevich]

[snip]
Is this right? Is there other efficent way to do it?
What about if data changes (i.e. new records are added) while the user
browses through pages?
[/snip]

on each page do a:

SELECT count(*) FROM table WHERE your_condition

this will give you your total number of results

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

RE: [PHP] mysql query with php {ot}

[Jay Blanchard]

[snip]
or just read the manual at mysql.com  .. 
[/snip]


Top posting.
What's annoying?

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

RE: [PHP] mysql query with php

[Jason Davidson]

or just read the manual at mysql.com  .. 

"Jay Blanchard" <[EMAIL PROTECTED]> wrote:
> 
> [snip]
> I just can't find the forum for any mysql. 
> [/snip]
> 
> http://www.mysql.com/doc/en/Mailing-list.html
> 
> [snip]
> anyways maybe someone run into this kind of problem, i'm saving in a db
> records of company and  in a column i have numbers i.e 1,2,12,31,32 so
> if want to search a company that has number 2 i do  category REGEXP '2'
> 
> but the problem it will return numbers 12, 32 but  I just want two.
> [/snip]
> 
> select company from table where category = '2'
> 
> --
> 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 with php

[Jay Blanchard]

[snip]
I just can't find the forum for any mysql. 
[/snip]

http://www.mysql.com/doc/en/Mailing-list.html

[snip]
anyways maybe someone run into this kind of problem, i'm saving in a db
records of company and  in a column i have numbers i.e 1,2,12,31,32 so
if want to search a company that has number 2 i do  category REGEXP '2'

but the problem it will return numbers 12, 32 but  I just want two.
[/snip]

select company from table where category = '2'

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

Re: [PHP] MySQL query

[Jason Wong]

On Wednesday 28 January 2004 12:05, Jason Giangrande wrote:
> I have the following query.
>
> UPDATE link SET hits = hits+1 WHERE website_link = '$link'
>
> $link is the website link that was clicked on.
>
> The query works fine.  The problem is if $link is a website that does
> not exist in the database mysql_query(); still returns true even though
> nothing was updated.  Why is this, 

Because mysql_query() returns FALSE only if there is an error in the query and 
something else otherwise. RTFM for details.

> and anyone have any suggestions on
> the easiest way to check to see if $link exists in the database before
> updating it?

  "select website_link from link where website_link = '$link'"

then use mysql_num_rows().

-- 
Jason Wong -> Gremlins Associates -> www.gremlins.biz
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
--
Search the list archives before you post
http://marc.theaimsgroup.com/?l=php-general
--
/*
Everything you know is wrong!
*/

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

Re: [PHP] MySQL query

[John W. Holmes]

Jason Giangrande wrote:

UPDATE link SET hits = hits+1 WHERE website_link = '$link'

$link is the website link that was clicked on.

The query works fine.  The problem is if $link is a website that does
not exist in the database mysql_query(); still returns true even though
nothing was updated.  Why is this, and anyone have any suggestions on
the easiest way to check to see if $link exists in the database before
updating it?
There's nothing technically wrong with the query, it simply doesn't 
match a link so nothing is updated. That doesn't mean the query fails. 
Anyhow, use mysql_affected_rows() to see if any rows were affected by 
your query. If it's zero, then the link probably doesn't exist.

--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/

php|architect: The Magazine for PHP Professionals – www.phparch.com

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

Re: [PHP] MySQL query

[CPT John W. Holmes]

From: "Ashley M. Kirchner" <[EMAIL PROTECTED]>

> >RD> SELECT whatever FROM table WHERE date_column_name BETWEEN '2004-01-09
> >RD> 00:00:00' AND '2004-01-04 23:59:59'
> >
> >Actually sorry, inverse the seconds (put the 00:00:00 onto the lower
> >date, the 4th) so it encompasses the whole period. You might actually
> >be able to not even include the seconds, try it and see what happens.
> >
> >
> Assume I don't know what those dates are.  I need to search based on
> whatever the current date is, and search between 2 and 7 days back.  The
> dates in my previous post were simply an example.

Should have said that in the first place. :)

SELECT * FROM table WHERE TO_DAYS(datetimefield) BETWEEN TO_DAYS(CURDATE() -
INTERVAL 2 DAY) AND TO_DAYS(CURDATE())

The "2" in the query can be a PHP variable ranging from 2 - 7, if you want.

---John Holmes...

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

Re: [PHP] MySQL query

[Ashley M. Kirchner]

Richard Davey wrote:

RD> SELECT whatever FROM table WHERE date_column_name BETWEEN '2004-01-09
RD> 00:00:00' AND '2004-01-04 23:59:59'
Actually sorry, inverse the seconds (put the 00:00:00 onto the lower
date, the 4th) so it encompasses the whole period. You might actually
be able to not even include the seconds, try it and see what happens.
 

   Assume I don't know what those dates are.  I need to search based on 
whatever the current date is, and search between 2 and 7 days back.  The 
dates in my previous post were simply an example.

--
W | I haven't lost my mind; it's backed up on tape somewhere.
 +
 Ashley M. Kirchner    .   303.442.6410 x130
 IT Director / SysAdmin / WebSmith . 800.441.3873 x130
 Photo Craft Laboratories, Inc.. 3550 Arapahoe Ave. #6
 http://www.pcraft.com . .  ..   Boulder, CO 80303, U.S.A.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] MySQL query

[CPT John W. Holmes]

From: "Ashley M. Kirchner" <[EMAIL PROTECTED]>

> I need to query for record within a certain date stamp.  The
> datetime field contains the createdon information that i need and is in
> the following format:  2004-01-11 21:40:50
>
> What I'd like to do is search for records that are between
> 2004-01-09 and 2004-01-04.  The time is to be ignored, just the date is
> what I need to search by.  Is there a way to do this within an sql
> query, or do I have to do some PHP processing of the datetime field to
> get what I need?

You'll want to do it in the query, although PHP could help a little.

Even though you want the time to be ignored, the easiest way to do this is
to actually search from 2004-01-09 00:00:00 through 2004-01-04 23:59:59.
Since the "00:00:00" and "23:59:59" are "constants" you can just have PHP
add them to the date string or hard code them into your query. Given that
format, the query would be as simple as:

SELECT * FROM table WHERE datetimefield BETWEEN $start AND $end

Where $start is "2004-01-09 00:00:00" and $end is "2004-01-04 23:59:59"

If you don't want to do that processing, then you'll need to use another
method.

SELECT * FROM table WHERE TO_DAYS(datetimefield) BETWEEN TO_DAYS($start) AND
TO_DAYS($end)

Where $start is "2004-01-09" and $end is "2004-01-04".

There are other methods, too, but the end results is you need to take your
"datetimefield" and get it into the same format as $start and $end.

If you're using MySQL 4.1, there is a DATE() function (I think) that'll
allow you to do this:

SELECT * FROM table WHERE DATE(datetimefield) BETWEEN $start AND $end

Where $start is "2004-01-09" and $end is "2004-01-04".

Hope that helps.

---John Holmes...

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