Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:35, Don Wieland d...@dwdataconcepts.com 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. -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:40, Daniel P. Brown daniel.br...@parasane.net wrote: For your convenience, both have been CC'd on this email. Actually, PHP-DB (php...@lists.php.net) was accidentally BCC'd. -- /Daniel P. Brown 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
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 sim...@syounger.com wrote: On Sat, 20 Nov 2010 13:54:29 -0700 Ben Miller biprel...@gmail.com 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 sim...@syounger.com -- 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
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 sim...@syounger.com wrote: On Sat, 20 Nov 2010 13:54:29 -0700 Ben Miller biprel...@gmail.com 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
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 Help
Since we are just tossing out development environments. We moved to Aptana in conjunction with TortoiseSVN for a team environment development timelines dropped. Personally I do not feel any gui editor makes you a better programmer, maybe you understand the fundamentals a little less. Not that anything so far has been an answer to your question. Developing mysql statements that not only require minimum resources but that are highly effective. Take a lot of trial and error. I feel there is no better tool in my mind to test query concepts than NaviCat. Not only is the product very user friendly but as a senior developer it gives me more insight into the impact the query has on my servers. Always think longevity of the product you are producing. Imagine one day you have 650,000 products how will the query impact the service you have written. I feel as a certified MySQL DBA you should understand that what works today may NOT be the best choice in query statements for the future. Always analyze your query statements for query length and system resources requirements. Depending on the structure of your database/tables/fields your query may be achieved many different ways. I might suggest you try an extended select statement. SELECT product from sometable WHERE product='$array1' AND product=(SELECT product_name from sometable where other matching critera); By extending or what some may call concating the statement the return is more effective. Richard L. Buskirk -Original Message- From: Ben Miller [mailto:biprel...@gmail.com] Sent: Saturday, November 20, 2010 3:54 PM To: 'php-general' Subject: [PHP] MySQL Query Help Hi, I'm building a website for a client in which I need to compare their products, side-by-side, but only include criteria for which all selected products have a value for that criteria. In my database (MySQL), I have a tables named products,criteria and criteria_values If I have something like $selected_product = array(1=Product 1,2=Product 2...) // All products selected for comparison by the user I need to get only rows from criteria where there is a row in criteria_values matching criteria.criteria_id for each $selected_product - in other words, if any of the $selected_product does not have a row in criteria_values that matches criteria.criteria_id, that criteria would not be returned. I hope that makes sense. I've played around with a few join queries, but none have given the desired results. Best I've been able to come up with so far is to query criteria for each DISTINCT(criteria_id) and then run through each $selected_product to make sure each has a criteria_value with a matching criteria_id, eliminating any criteria where the number of criteria_values count($selected_product), but this seems pretty inefficient. Thanks in advance for any help. Ben Miller -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Help
On Sat, 20 Nov 2010 13:54:29 -0700 Ben Miller biprel...@gmail.com 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 sim...@syounger.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
I am very keen to see a closure to this thread so that I can add to my snippets. Let's all know what worked best out of many solutions that have been proposed. --Shreyas On Tue, Jul 20, 2010 at 10:07 AM, Jim Lucas li...@cmsws.com 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
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
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
On 19 July 2010 05:44, Peter pet...@egrabber.com 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
On 19 July 2010 15:01, Richard Quadling rquadl...@gmail.com wrote: On 19 July 2010 05:44, Peter pet...@egrabber.com 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
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 rquadl...@gmail.comwrote: On 19 July 2010 05:44, Peter pet...@egrabber.com 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
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 shreya...@gmail.comwrote: 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 rquadl...@gmail.comwrote: On 19 July 2010 05:44, Peter pet...@egrabber.com 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
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
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 Puzzle
On Mon, Jul 19, 2010 at 10:44 AM, Peter pet...@egrabber.com 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 not working!
On Wed, 2010-03-31 at 16:20 +0430, Parham Doustdar wrote:
Hi there,
Here is a snippet of code... that doesn't work for some reason. Please note
that I have put some
@mysql_query($query) or die(mysql_error());
statements, to see if MySQL gives an error. I receive nothing other than the
file starting to download. This is supposed to be a file download counter:
[code]
?php
//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
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; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net 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]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' . $_GET['file'] .
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = ' .
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
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 an...@oire.org
To: Parham Doustdar parha...@gmail.com
Cc: php-general@lists.php.net
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; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net 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]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' . $_GET['file']
.
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = ' .
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
Hi!
To view error:
you must use mysql_query(). @ before mysql - error supression.
next
you can use the following:
$result=..
if($result){
if(mysql_num_rows($result)){
/* you have record in table */
}else{
/* you haven't */
On Wed, Mar 31, 2010 at 4:11 PM, Andre Polykanine an...@oire.org wrote:
Hello Parham,
Adding to Ash's question, why to use the @ operator before
mysql_query?
--
With best regards from Ukraine,
Andre
Skype: Francophile; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net 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]
?php
//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!
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 an...@oire.org
To: Parham Doustdar parha...@gmail.com
Cc: php-general@lists.php.net
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; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net 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]
?php
//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!
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
a...@ashleysheridan.co.ukwrote:
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 an...@oire.org
To: Parham Doustdar parha...@gmail.com
Cc: php-general@lists.php.net
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; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon
@
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net 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]
?php
//connect to the DB
mysql_connect() //There is no problem with the connection so I didn't
include the complete code.
//The table where the hits are stored.
$table = files;
$query = select * from . $table . where filename = ' .
$_GET['file']
.
';
$result = mysql_query($query);
if ($result) //Has the file previously been added?
{
$query = update . $table . set hits = hits + 1 where filename = '
.
$_GET['file'] . ';
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
else //it's the first time we're adding this file to the DB.
{
$query = insert into . $table . (filename, hits) values (' .
$_GET['file'] . ', 1);
@mysql_query($query) or die(mysql_error());
header('location:http://www.qwitter-client.net/' . $_GET['file']);
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
My understanding of the @ here would be that PHP won't register the
error, so it won't ever die()
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] MySQL query not working!
On Wed, Mar 31, 2010 at 8:46 AM, Ashley Sheridan
a...@ashleysheridan.co.uk 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 an...@oire.org
To: Parham Doustdar parha...@gmail.com
Cc: php-general@lists.php.net
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; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon @
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net 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]
?php
//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:
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
Could not connect
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
br /
bWarning/b: mysql_connect() [a
href='function.mysql-connect'function.mysql-connect/a]: Can't
connect to MySQL server on 'localhost' (10061) in bPHPDocument1/b
on line b3/bbr /
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!
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 aball...@gmail.com wrote:
On Wed, Mar 31, 2010 at 8:46 AM, Ashley Sheridan
a...@ashleysheridan.co.uk 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 an...@oire.org
To: Parham Doustdar parha...@gmail.com
Cc: php-general@lists.php.net
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; WlmMSN: arthaelon @ yandex.ru; Jabber: arthaelon
@
jabber.org
Yahoo! messenger: andre.polykanine; ICQ: 191749952
Twitter: m_elensule
- Original message -
From: Parham Doustdar parha...@gmail.com
To: php-general@lists.php.net 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]
?php
//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:
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
Could not connect
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
br /
bWarning/b: mysql_connect() [a
href='function.mysql-connect'function.mysql-connect/a]: Can't
connect to MySQL server on 'localhost' (10061) in bPHPDocument1/b
on line b3/bbr /
Could not connect
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query not working!
On Wed, Mar 31, 2010 at 9:08 AM, Andrew Ballard aball...@gmail.com wrote:
Nope. All it does is suppress the error message. Just try it:
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
Could not connect
?php
@mysql_connect('localhost', 'baduser', 'badpassword') or die('Could
not connect');
?
Output:
br /
bWarning/b: mysql_connect() [a
href='function.mysql-connect'function.mysql-connect/a]: Can't
connect to MySQL server on 'localhost' (10061) in bPHPDocument1/b
on line b3/bbr /
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!
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]
?php
//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']);
}
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
[PHP] Re: php/mysql Query Question.
ad...@buskirkgraphics.com wrote: Before most of you go on a rampage of how to please read below... As most of you already know when using MySQL from the shell you can write your queries in html format in an out file. Example: shellmysql -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 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
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
Michael S. Dunsavage wrote: On Fri, 2008-11-14 at 12:46 -0800, Jim Lucas wrote: SELECT @confirm_number AS confirm_number; Are we not SELECTING the column value here? should we be selecting confirm_number as confirm_number? The idea is to give you the number that was used in the INSERT statement. It might have changed since the INSERT. Never know. So, giving you the one used in the INSERT is the best way to make sure you get the one you are expecting. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
On Fri, 2008-11-14 at 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
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
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
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
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
[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
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
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
[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
?php
#
# 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
)
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 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
?php
#
# 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
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
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
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
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
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 question
On Fri, 2008-11-14 at 00:52 -0600, Micah Gersten wrote: If you're just adding one, there is no reason to retrieve the data, process it, and update it. You can just update the number. http://dev.mysql.com/doc/refman/5.0/en/update.html But, the problem is that the confirm_number is a confirmation number that gets e-mailed out. So I have to pull it from the DB any way -- Michael S. Dunsavage -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mySQL query question
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 and maximum characters in sql statement
Hi Jim Lucas, You are correct... i want to run in the same way. but as my 2 tables, column name are different i cant run the LOAD DATA infile. And the example you mentioned for break at 100, also i thought to use in that way. but one of the column had the text type which we cant predict about the size. Thanks for the support from all of you. Now, I am inserting the rows one by one only On 5/2/08, Chris [EMAIL PROTECTED] wrote: Jim Lucas wrote: Waynn Lue wrote: Wouldn't using LOAD DATA INFILE be better than writing your own script? depends, does the data file match the table column for column? Doesn't have to. http://dev.mysql.com/doc/refman/5.0/en/load-data.html By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list: LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...); But load data infile requires extra mysql privileges. -- Postgresql php tutorials http://www.designmagick.com/ -- Regards, Sanjeev http://www.sanchanworld.com | http://webdirectory.sanchanworld.com - submit your site
Re: [PHP] mysql query and maximum characters in sql statement
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
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
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
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; $isizeof($array); $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
?php
// read file
// for each line, do the following
while ($in != EOF)
{
$line=explode (',',$in);
$out=insert into table values('','$line[0]', .);
fwrite ($outf, $out);
}
then I have another file that
?php
//read the file line by line
//connect to db
$sql = $inLine;
mysql_query($sql) or die();
?
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 and maximum characters in sql statement
Sanjeev N wrote:
Hi,
I have written a program which imports the tab delimited file and insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $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.
?php
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;
}
?
--
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 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; $isizeof($array); $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.
?php
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.
?php
# 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
Re: [PHP] mysql query and maximum characters in sql statement
Wouldn't using LOAD DATA INFILE be better than writing your own script?
On 5/1/08, Jim Lucas [EMAIL PROTECTED] wrote:
Jim Lucas wrote:
Sanjeev N wrote:
Hi,
I have written a program which imports the tab delimited file and
insert all
the line from file to the mysql line by line.
I am succeding in the above case. but problem with the above method is
its
taking to too much time while inserting into the database.
The file's size will be more than 5000 lines.
Then i tried to build a string of ; seperated queries. as follows
for($i=1; $isizeof($array); $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.
?php
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.
?php
# 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/$post problem
Mark a écrit : I havnt even tried this query but i know its wrong can anyone help! *** ?php include(header.php); include(connect.php); 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/$post problem
On 3/27/06, Mark [EMAIL PROTECTED] wrote:
I havnt even tried this query but i know its wrong can anyone help!
***
?php
include(header.php);
include(connect.php);
$comp_id = $_SESSION['comp_id'];
$user_id = $_SESSION['user_id'];
// Grab variables and insert into database
$avname = $_POST['avname'];
$query = INSERT INTO users AVATARS WHERE id =$user_id '','$avname');
The format for insert queries is:
insert into table(field1, field2) values ('value1', 'value2')
or
insert into table set field1=value2, field2=value2 etc.
What exactly is the tablename? Also you don't need the ) on the end:
$query = INSERT INTO tablename SET id =$user_id '','$avname';
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query/$post problem
Mark skrev:
I havnt even tried this query but i know its wrong can anyone help!
***
?php
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');
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.
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
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
On Wednesday 14 September 2005 07:36 pm, Jesús Alain Rodríguez Santos wrote:
I have a table colum in mysql with two fields: day and month. I
would like to know if it's possible to make a query where I can
determine if exist days before to a selected day, for example:
if I have in my table:
day 19 - month 05, I wish to know if there are previous days
inserted at the 19, the days they are not inserted in the table,
they are inserted according your selection, what I want to get is
that every time that you insert a day, I want to check if there
are days previous to the one already inserted in the table in the
same month, in case that there are not them then they owe you
to insert together with the one selected,
I haven't tried this, but the logic should work according to the manual:
You don't have to check, you can just insert all the data. If the row
already exists, the data will not be inserted. This assumes that you have a
constraint on the table which prevents duplicate values!
$day=19;
$month=5;
for($i=1;i=$day;i++){
$result=mysql_query(INSERT INTO table (month,day) VALUES (5,$i));
}
If you don't have a constraint, then you will have to loop over the data for
that month and insert the rows where they don't already exist.
I wait they understand me what I want:
I work php/mysql.
create table tableA (
day int,
month int
);
select * from tableA where month=5 and day 19;
This will select everything from the 5th month and before the 19th day of
the
5th month.
Is that what you were going for?
sorry for my english i'm cuban
Thank you and excuse the nuisances
--
Este mensaje ha sido analizado por MailScanner
en busca de virus y otros contenidos peligrosos,
y se considera que está limpio.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query
On Wednesday 14 September 2005 07:36 pm, Jesús Alain Rodríguez Santos wrote: I have a table colum in mysql with two fields: day and month. I would like to know if it's possible to make a query where I can determine if exist days before to a selected day, for example: if I have in my table: day 19 - month 05, I wish to know if there are previous days inserted at the 19, the days they are not inserted in the table, they are inserted according your selection, what I want to get is that every time that you insert a day, I want to check if there are days previous to the one already inserted in the table in the same month, in case that there are not them then they owe you to insert together with the one selected, I wait they understand me what I want: I work php/mysql. create table tableA ( day int, month int ); select * from tableA where month=5 and day 19; This will select everything from the 5th month and before the 19th day of the 5th month. Is that what you were going for? sorry for my english i'm cuban Thank you and excuse the nuisances -- Este mensaje ha sido analizado por MailScanner en busca de virus y otros contenidos peligrosos, y se considera que está limpio. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query update two table in one?
Tyler Replogle schrieb: Can you update two tables in one mysql query i've got these two queries $db-query(update `dbn_members_counters` set views =(views +1) where id = '$this-id' ); $db-query(update `dbn_members` set lastaction = $conf-site_time, page = '$this-page' where id = '$this-id' ); and i was wonder if i could get them into one because there are right next two each other and i'm trying to lower my query count. Yes, you can do that. BUT you need mySQL 4.0 for this! Use a JOIN. Have a look at: http://dev.mysql.com/doc/mysql/en/update.html Best regards, Christian From: K Karthik [EMAIL PROTECTED] To: php-general@lists.php.net Subject: [PHP] php-help Date: Thu, 10 Mar 2005 12:31:35 +0530 MIME-Version: 1.0 Received: from mc7-f40.hotmail.com ([65.54.253.47]) by IMC3-S26.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); Wed, 9 Mar 2005 22:59:41 -0800 Received: from lists.php.net ([216.92.131.4]) by mc7-f40.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); Wed, 9 Mar 2005 22:59:41 -0800 Received: from ([216.92.131.4:25901] helo=lists.php.net)by pb1.pair.com (ecelerity HEAD r(5124)) with SMTPid E8/F4-53294-F40FF224 for [EMAIL PROTECTED]; Thu, 10 Mar 2005 01:59:30 -0500 Received: (qmail 17042 invoked by uid 1010); 10 Mar 2005 06:58:09 - Received: (qmail 17024 invoked by uid 1010); 10 Mar 2005 06:58:09 - X-Message-Info: JGTYoYF78jFevGptXBXjGwKBSvHljkD+bF1qvT/FEkQ= Return-Path: [EMAIL PROTECTED] X-Host-Fingerprint: 216.92.131.4 lists.php.net Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm Precedence: bulk list-help: mailto:[EMAIL PROTECTED] list-unsubscribe: mailto:[EMAIL PROTECTED] list-post: mailto:php-general@lists.php.net Delivered-To: mailing list php-general@lists.php.net Delivered-To: [EMAIL PROTECTED] Delivered-To: [EMAIL PROTECTED] X-Host-Fingerprint: 203.193.155.110 alps.manageengine.org Linux 2.4/2.6 User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20040913 X-Accept-Language: en-us, en X-OriginalArrivalTime: 10 Mar 2005 06:59:41.0597 (UTC) FILETIME=[BBA044D0:01C5253E] sir, i'll explain my problem.and if anyone could help me i'll be thankful. i am displaying a content read from a file into a text area of a form. when i make changes andretrieve back in the text area, i encounter a problem.. i.e., when i enter text=please enter i have an outputas text= / please enter/ thanks, kkarthik -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query
Sebastian wrote:
Hello,
im working on an article system and looking to avoid running three queries.
example, i have this query:
SELECT id,title FROM articles WHERE id=$_GET[id]
now say $_GET[id] = 5
I would like to get the previous id 4 and the next id 6 (if there is one)
so i can do something like:
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
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;
previous $title[$prev ]
next $title[$next]
?php
/*
Stephen Johnson c|eh
The Lone Coder
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: php-general@lists.php.net
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
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
On 17 Feb 2005 19:28:18 -0600, Bret Hughes [EMAIL PROTECTED] wrote: On Thu, 2005-02-17 at 18:24, Sebastian wrote: Hello, im working on an article system and looking to avoid running three queries. example, i have this query: SELECT id,title FROM articles WHERE id=$_GET[id] now say $_GET[id] = 5 I would like to get the previous id 4 and the next id 6 (if there is one) so i can do something like: I would do something like: $theId = $_GET['id']; $ids = $theId-1 . , . $theId , . $theId+1; $query = SELECT id, title FROM articles WHERE id IN ($ids); -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql query
On Thu, 2005-02-17 at 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 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
RE: [PHP] MYSQL Query question
First, since it was a combined question of php and mysql I thought of sending it here. Secondly, this is my standard footer. My appologizes if my question irritates you -Original Message- From: Raditha Dissanayake [mailto:[EMAIL PROTECTED] Sent: donderdag 9 december 2004 14:14 To: [EMAIL PROTECTED] Subject: Re: [PHP] MYSQL Query question Reinhart Viane wrote: And a last question: I always seem to get stuck on mysql queries when scripting. mysql.com gives me a headache whens earching something. Does someone know a good mysql manual site or a good mysql book? That does not mean mysql questions should be posted on php mailing lists. STRICTLY PERSONAL AND CONFIDENTIAL This message may contain confidential and proprietary material for the sole use of the intended recipient. Any review or distribution by others is strictly prohibited. If you are not the intended recipient please contact the sender and delete all copies. Do you know that mailing lists are automatically archived at thousands of websites? -- Raditha Dissanayake. -- http://www.radinks.com/print/card-designer/ | Card Designer Applet http://www.radinks.com/upload/ | Drag and Drop Upload -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MYSQL Query question
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. snip 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
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. snip 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 with exclude
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
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
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 for average records per day
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 for average records per day
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
[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
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 OT
[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
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
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 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
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 OT
[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 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 h3active members should appear here/h3;
$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]
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 h3active members should appear here/h3;
$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]
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 h3active members should appear here/h3;
$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
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
[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
[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 with php
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 {ot}
[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
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
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
Hello Ashley, Monday, January 12, 2004, 8:21:01 PM, you wrote: AMK What I'd like to do is search for records that are between AMK 2004-01-09 and 2004-01-04. The time is to be ignored, just the date is AMK what I need to search by. Is there a way to do this within an sql AMK query, or do I have to do some PHP processing of the datetime field to AMK get what I need? SELECT whatever FROM table WHERE date_column_name BETWEEN '2004-01-09 00:00:00' AND '2004-01-04 23:59:59' -- Best regards, Richardmailto:[EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL query
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
Re: [PHP] MySQL query
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 mailto:[EMAIL PROTECTED] . 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
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
Hi,
Friday, October 3, 2003, 8:17:23 PM, you wrote:
CM ok im stumped lol i have used this code in the past to insert data into
CM mysql (im relitively new though)
CM --code
CM mysql_query(INSERT INTO Images (Image, desc) VALUES ('$name',
CM '$description')) or die (mysql_error());
CM --end code-
CM but i get this error]
CM -error-
CM You have an error in your SQL syntax. Check the manual that corresponds to
CM your MySQL server version for the right syntax to use near ''Image', 'desc')
CM VALUES ('2419091.jpg', 'stone')' at line 1
CM --- end error--
CM I found if i just try and record the name value it works fine but if i try
CM to add the description i get the error.
CM Any clues?
CM :)
it may be because desc is a reserved word, put it in backticks `desc`
--
regards,
Tom
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
