Re: [PHP-DB] Newbie Question $2
On 6/18/2014 12:31 AM, Ethan Rosenberg, PhD wrote: On 06/17/2014 12:02 PM, onatawah...@yahoo.ca wrote: Hi Ethan, Here are some things to clean up your code: Your line: $phn = $_POST[phone]; should use quotations as follows: $phn = $_POST['phone']; Your line: $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; Should use double quotes if you need the variable to be interpreted: $sql1 =select Lname, Fname from Customers where Phone = $Phn ; Lastly, as people have mentioned PDO is probably the best way to go. Try connecting to your database with PDO. Look on Google for PDO prepared statements and use those instead of the mysql escape string method. Hope this helps, -Kevin Sent from Yahoo Mail on Android IT WORKS!!! Here is the code - !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; html ?php $bla = 1; ? head /head body div align=center form method=post input type='text' name=phone/input input type='submit' br /br /br / /form /div ?php error_reporting(-1); require '/home/ethan/PHP/ethan.inc'; $db = Store; $cxn = mysqli_connect($host,$user,$password,$db); $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$phn[6].$phn[7].$phn[8].$phn[9]; $sql1 =select Lname, Fname from Customers where Phone = '$Phn' ; $result1 = mysqli_query($cxn, $sql1); if(!$result) { ? div align=center strongNo Match Found/strong br /br / /div ?php } ? div align=center table border=4 cellpadding=5 cellspacing=55 rules=all frame=box tr class='heading' thLast Name/th thFirst Name/th ?php while($row1 = mysqli_fetch_row($result1)) { $Lname = $row1[0]; $Fname = $row1[1]; ? tr td ?php echo $Lname; ? /td td ?php echo $Fname; ? /td /tr ?php } ? /table /div /body /html As you [those that replied] accurately noted, the problem was with the quoting. I appreciate all your comments, take them seriously and will use the information contained in them for future programming. No matter how much skill in programming I have, I will remain a NEWBIE; ie, someone who wishes to grrow in knowledge and acknowledges that there are many programmers much more skilled than I. Thanks again. Ethan happy to hear you got it working. Sad to see that you didn't heed the tips provided to you and alter your code, and that you still have errors in it. oh, well -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie Question $2
Sent from losPhone On Jun 18, 2014, at 7:56 AM, Jim Giner jim.gi...@albanyhandball.com wrote: On 6/18/2014 12:31 AM, Ethan Rosenberg, PhD wrote: On 06/17/2014 12:02 PM, onatawah...@yahoo.ca wrote: Hi Ethan, Here are some things to clean up your code: Your line: $phn = $_POST[phone]; should use quotations as follows: $phn = $_POST['phone']; Your line: $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; Should use double quotes if you need the variable to be interpreted: $sql1 =select Lname, Fname from Customers where Phone = $Phn ; Lastly, as people have mentioned PDO is probably the best way to go. Try connecting to your database with PDO. Look on Google for PDO prepared statements and use those instead of the mysql escape string method. Hope this helps, -Kevin Sent from Yahoo Mail on Android IT WORKS!!! Here is the code - !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; html ?php $bla = 1; ? head /head body div align=center form method=post input type='text' name=phone/input input type='submit' br /br /br / /form /div ?php error_reporting(-1); require '/home/ethan/PHP/ethan.inc'; $db = Store; $cxn = mysqli_connect($host,$user,$password,$db); $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$phn[6].$phn[7].$phn[8].$phn[9]; $sql1 =select Lname, Fname from Customers where Phone = '$Phn' ; $result1 = mysqli_query($cxn, $sql1); if(!$result) { ? div align=center strongNo Match Found/strong br /br / /div ?php } ? div align=center table border=4 cellpadding=5 cellspacing=55 rules=all frame=box tr class='heading' thLast Name/th thFirst Name/th ?php while($row1 = mysqli_fetch_row($result1)) { $Lname = $row1[0]; $Fname = $row1[1]; ? tr td ?php echo $Lname; ? /td td ?php echo $Fname; ? /td /tr ?php } ? /table /div /body /html As you [those that replied] accurately noted, the problem was with the quoting. I appreciate all your comments, take them seriously and will use the information contained in them for future programming. No matter how much skill in programming I have, I will remain a NEWBIE; ie, someone who wishes to grrow in knowledge and acknowledges that there are many programmers much more skilled than I. Thanks again. Ethan happy to hear you got it working. Sad to see that you didn't heed the tips provided to you and alter your code, and that you still have errors in it. oh, well Wow. Just wow. I though when I signed up on this list that if I did what Ethan did I would be shunned from the list. But I guess I was wrong. You can be an ask hole on here and people will still try and help. Kudos to the good souls who try. Karl -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie Question $2
On Wed, Jun 18, 2014 at 2:13 PM, Karl DeSaulniers k...@designdrumm.com wrote: Sent from losPhone On Jun 18, 2014, at 7:56 AM, Jim Giner jim.gi...@albanyhandball.com wrote: On 6/18/2014 12:31 AM, Ethan Rosenberg, PhD wrote: On 06/17/2014 12:02 PM, onatawah...@yahoo.ca wrote: Hi Ethan, Here are some things to clean up your code: Your line: $phn = $_POST[phone]; should use quotations as follows: $phn = $_POST['phone']; Your line: $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; Should use double quotes if you need the variable to be interpreted: $sql1 =select Lname, Fname from Customers where Phone = $Phn ; Lastly, as people have mentioned PDO is probably the best way to go. Try connecting to your database with PDO. Look on Google for PDO prepared statements and use those instead of the mysql escape string method. Hope this helps, -Kevin Sent from Yahoo Mail on Android IT WORKS!!! Here is the code - !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; html ?php $bla = 1; ? head /head body div align=center form method=post input type='text' name=phone/input input type='submit' br /br /br / /form /div ?php error_reporting(-1); require '/home/ethan/PHP/ethan.inc'; $db = Store; $cxn = mysqli_connect($host,$user,$password,$db); $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$phn[6].$phn[7].$phn[8].$phn[9]; $sql1 =select Lname, Fname from Customers where Phone = '$Phn' ; $result1 = mysqli_query($cxn, $sql1); if(!$result) { ? div align=center strongNo Match Found/strong br /br / /div ?php } ? div align=center table border=4 cellpadding=5 cellspacing=55 rules=all frame=box tr class='heading' thLast Name/th thFirst Name/th ?php while($row1 = mysqli_fetch_row($result1)) { $Lname = $row1[0]; $Fname = $row1[1]; ? tr td ?php echo $Lname; ? /td td ?php echo $Fname; ? /td /tr ?php } ? /table /div /body /html As you [those that replied] accurately noted, the problem was with the quoting. I appreciate all your comments, take them seriously and will use the information contained in them for future programming. No matter how much skill in programming I have, I will remain a NEWBIE; ie, someone who wishes to grrow in knowledge and acknowledges that there are many programmers much more skilled than I. Thanks again. Ethan happy to hear you got it working. Sad to see that you didn't heed the tips provided to you and alter your code, and that you still have errors in it. oh, well Wow. Just wow. I though when I signed up on this list that if I did what Ethan did I would be shunned from the list. But I guess I was wrong. You can be an ask hole on here and people will still try and help. Kudos to the good souls who try. Karl -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php There are lots of people who have free time on their hands to teach the basics, which I think is a good thing. Personally, if someone doesn't care enough to read the manual or attempt to understand the basics, I wouldn't spend too much time on their problems.
Re: [PHP-DB] Newbie Question $2
On 6/18/2014 2:16 PM, Aziz Saleh wrote: On Wed, Jun 18, 2014 at 2:13 PM, Karl DeSaulniers k...@designdrumm.com wrote: Sent from losPhone On Jun 18, 2014, at 7:56 AM, Jim Giner jim.gi...@albanyhandball.com wrote: On 6/18/2014 12:31 AM, Ethan Rosenberg, PhD wrote: On 06/17/2014 12:02 PM, onatawah...@yahoo.ca wrote: Hi Ethan, Here are some things to clean up your code: Your line: $phn = $_POST[phone]; should use quotations as follows: $phn = $_POST['phone']; Your line: $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; Should use double quotes if you need the variable to be interpreted: $sql1 =select Lname, Fname from Customers where Phone = $Phn ; Lastly, as people have mentioned PDO is probably the best way to go. Try connecting to your database with PDO. Look on Google for PDO prepared statements and use those instead of the mysql escape string method. Hope this helps, -Kevin Sent from Yahoo Mail on Android IT WORKS!!! Here is the code - !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; html ?php $bla = 1; ? head /head body div align=center form method=post input type='text' name=phone/input input type='submit' br /br /br / /form /div ?php error_reporting(-1); require '/home/ethan/PHP/ethan.inc'; $db = Store; $cxn = mysqli_connect($host,$user,$password,$db); $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$phn[6].$phn[7].$phn[8].$phn[9]; $sql1 =select Lname, Fname from Customers where Phone = '$Phn' ; $result1 = mysqli_query($cxn, $sql1); if(!$result) { ? div align=center strongNo Match Found/strong br /br / /div ?php } ? div align=center table border=4 cellpadding=5 cellspacing=55 rules=all frame=box tr class='heading' thLast Name/th thFirst Name/th ?php while($row1 = mysqli_fetch_row($result1)) { $Lname = $row1[0]; $Fname = $row1[1]; ? tr td ?php echo $Lname; ? /td td ?php echo $Fname; ? /td /tr ?php } ? /table /div /body /html As you [those that replied] accurately noted, the problem was with the quoting. I appreciate all your comments, take them seriously and will use the information contained in them for future programming. No matter how much skill in programming I have, I will remain a NEWBIE; ie, someone who wishes to grrow in knowledge and acknowledges that there are many programmers much more skilled than I. Thanks again. Ethan happy to hear you got it working. Sad to see that you didn't heed the tips provided to you and alter your code, and that you still have errors in it. oh, well Wow. Just wow. I though when I signed up on this list that if I did what Ethan did I would be shunned from the list. But I guess I was wrong. You can be an ask hole on here and people will still try and help. Kudos to the good souls who try. Karl -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php There are lots of people who have free time on their hands to teach the basics, which I think is a good thing. Personally, if someone doesn't care enough to read the manual or attempt to understand the basics, I wouldn't spend too much time on their problems. And despite Ethan's continual ignorance of the manual and the basic principles espoused by those taking the time to respond to him we still do it. Aren't we all amazing? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie Question $2
Can we kill this thread? Or can you guys continue the conversation between yourselves. We now have 8 emails pertaining to the technical question, and 8 emails ranting about him asking it. I understand that some people do not believe this is the appropriate forum for that question - but personal attacks and rantings accomplish nothing other than to provide bully tactics and express your outrage at list spamming while spamming the list. Thank you for your consideration. - Mike On Wed, Jun 18, 2014 at 11:49 AM, Jim Giner jim.gi...@albanyhandball.com wrote: On 6/18/2014 2:16 PM, Aziz Saleh wrote: On Wed, Jun 18, 2014 at 2:13 PM, Karl DeSaulniers k...@designdrumm.com wrote: Sent from losPhone On Jun 18, 2014, at 7:56 AM, Jim Giner jim.gi...@albanyhandball.com wrote: On 6/18/2014 12:31 AM, Ethan Rosenberg, PhD wrote: On 06/17/2014 12:02 PM, onatawah...@yahoo.ca wrote: Hi Ethan, Here are some things to clean up your code: Your line: $phn = $_POST[phone]; should use quotations as follows: $phn = $_POST['phone']; Your line: $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; Should use double quotes if you need the variable to be interpreted: $sql1 =select Lname, Fname from Customers where Phone = $Phn ; Lastly, as people have mentioned PDO is probably the best way to go. Try connecting to your database with PDO. Look on Google for PDO prepared statements and use those instead of the mysql escape string method. Hope this helps, -Kevin Sent from Yahoo Mail on Android IT WORKS!!! Here is the code - !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; html ?php $bla = 1; ? head /head body div align=center form method=post input type='text' name=phone/input input type='submit' br /br /br / /form /div ?php error_reporting(-1); require '/home/ethan/PHP/ethan.inc'; $db = Store; $cxn = mysqli_connect($host,$user,$password,$db); $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$ phn[6].$phn[7].$phn[8].$phn[9]; $sql1 =select Lname, Fname from Customers where Phone = '$Phn' ; $result1 = mysqli_query($cxn, $sql1); if(!$result) { ? div align=center strongNo Match Found/strong br /br / /div ?php } ? div align=center table border=4 cellpadding=5 cellspacing=55 rules=all frame=box tr class='heading' thLast Name/th thFirst Name/th ?php while($row1 = mysqli_fetch_row($result1)) { $Lname = $row1[0]; $Fname = $row1[1]; ? tr td ?php echo $Lname; ? /td td ?php echo $Fname; ? /td /tr ?php } ? /table /div /body /html As you [those that replied] accurately noted, the problem was with the quoting. I appreciate all your comments, take them seriously and will use the information contained in them for future programming. No matter how much skill in programming I have, I will remain a NEWBIE; ie, someone who wishes to grrow in knowledge and acknowledges that there are many programmers much more skilled than I. Thanks again. Ethan happy to hear you got it working. Sad to see that you didn't heed the tips provided to you and alter your code, and that you still have errors in it. oh, well Wow. Just wow. I though when I signed up on this list that if I did what Ethan did I would be shunned from the list. But I guess I was wrong. You can be an ask hole on here and people will still try and help. Kudos to the good souls who try. Karl -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php There are lots of people who have free time on their hands to teach the basics, which I think is a good thing. Personally, if someone doesn't care enough to read the manual or attempt to understand the basics, I wouldn't spend too much time on their problems. And despite Ethan's continual ignorance of the manual and the basic principles espoused by those taking the time to respond to him we still do it. Aren't we all amazing? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- --- My command is this: Love each other as I have loved you. John 15:12
Re: [PHP-DB] Newbie Question $2
Hi Ethan, Here are some things to clean up your code: Your line: $phn = $_POST[phone]; should use quotations as follows: $phn = $_POST['phone']; Your line: $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; Should use double quotes if you need the variable to be interpreted: $sql1 =select Lname, Fname from Customers where Phone = $Phn ; Lastly, as people have mentioned PDO is probably the best way to go. Try connecting to your database with PDO. Look on Google for PDO prepared statements and use those instead of the mysql escape string method. Hope this helps, -Kevin Sent from Yahoo Mail on Android
Re: [PHP-DB] Newbie Question $2
On 06/17/2014 12:02 PM, onatawah...@yahoo.ca wrote: Hi Ethan, Here are some things to clean up your code: Your line: $phn = $_POST[phone]; should use quotations as follows: $phn = $_POST['phone']; Your line: $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; Should use double quotes if you need the variable to be interpreted: $sql1 =select Lname, Fname from Customers where Phone = $Phn ; Lastly, as people have mentioned PDO is probably the best way to go. Try connecting to your database with PDO. Look on Google for PDO prepared statements and use those instead of the mysql escape string method. Hope this helps, -Kevin Sent from Yahoo Mail on Android IT WORKS!!! Here is the code - !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; html ?php $bla = 1; ? head /head body div align=center form method=post input type='text' name=phone/input input type='submit' br /br /br / /form /div ?php error_reporting(-1); require '/home/ethan/PHP/ethan.inc'; $db = Store; $cxn = mysqli_connect($host,$user,$password,$db); $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$phn[6].$phn[7].$phn[8].$phn[9]; $sql1 =select Lname, Fname from Customers where Phone = '$Phn' ; $result1 = mysqli_query($cxn, $sql1); if(!$result) { ? div align=center strongNo Match Found/strong br /br / /div ?php } ? div align=center table border=4 cellpadding=5 cellspacing=55 rules=all frame=box tr class='heading' thLast Name/th thFirst Name/th ?php while($row1 = mysqli_fetch_row($result1)) { $Lname = $row1[0]; $Fname = $row1[1]; ? tr td ?php echo $Lname; ? /td td ?php echo $Fname; ? /td /tr ?php } ? /table /div /body /html As you [those that replied] accurately noted, the problem was with the quoting. I appreciate all your comments, take them seriously and will use the information contained in them for future programming. No matter how much skill in programming I have, I will remain a NEWBIE; ie, someone who wishes to grrow in knowledge and acknowledges that there are many programmers much more skilled than I. Thanks again. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie Question $2
Dear List - I have the following code: The input from the form is a 10 digit string [1234567890] which is converted to phone number format [123-456-7890] $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$phn[6].$phn[7].$phn[8].$phn[9]; echo $Phn; // this is folded by Thunderbird. In the script, it is //all on one line mysql_real_escape_string($Phn); $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; echo $sql1; //this always shows $phn as Phn and not as a numerical //string. $result1 = mysqli_query($cxn, $sql1); TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie Question $2
On Jun 16, 2014, at 9:58 PM, Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote: Dear List - I have the following code: The input from the form is a 10 digit string [1234567890] which is converted to phone number format [123-456-7890] $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$phn[6].$phn[7].$phn[8].$phn[9]; echo $Phn; // this is folded by Thunderbird. In the script, it is //all on one line mysql_real_escape_string($Phn); $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; echo $sql1; //this always shows $phn as Phn and not as a numerical //string. $result1 = mysqli_query($cxn, $sql1); TIA Ethan Well, from first glance you're combining mysql and mysqli. Don't know if that is wise or permissible since I think mysql has been depreciated. Go with mysqli. Next you may want to try... $sql1 = 'SELECT Lname, Fname FROM Customers WHERE Phone = '.$Phn; Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie Question $2
On Mon, Jun 16, 2014 at 10:58 PM, Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote: Dear List - I have the following code: The input from the form is a 10 digit string [1234567890] which is converted to phone number format [123-456-7890] $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$ phn[6].$phn[7].$phn[8].$phn[9]; echo $Phn; // this is folded by Thunderbird. In the script, it is //all on one line mysql_real_escape_string($Phn); $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; echo $sql1; //this always shows $phn as Phn and not as a numerical //string. $result1 = mysqli_query($cxn, $sql1); TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php This page should help you: http://www.php.net//manual/en/language.types.string.php understand the difference between single and double quotes.
Re: [PHP-DB] Newbie Question $2
On Jun 16, 2014, at 10:05 PM, Karl DeSaulniers k...@designdrumm.com wrote: On Jun 16, 2014, at 9:58 PM, Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote: Dear List - I have the following code: The input from the form is a 10 digit string [1234567890] which is converted to phone number format [123-456-7890] $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$phn[6].$phn[7].$phn[8].$phn[9]; echo $Phn; // this is folded by Thunderbird. In the script, it is //all on one line mysql_real_escape_string($Phn); $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; echo $sql1; //this always shows $phn as Phn and not as a numerical //string. $result1 = mysqli_query($cxn, $sql1); TIA Ethan Well, from first glance you're combining mysql and mysqli. Don't know if that is wise or permissible since I think mysql has been depreciated. Go with mysqli. Next you may want to try... $sql1 = 'SELECT Lname, Fname FROM Customers WHERE Phone = '.$Phn; Best, Karl DeSaulniers Design Drumm http://designdrumm.com Also, you may want to store the number in your database without the dash and just apply the dash when displaying the number in HTML. Not that this is entirely necessary, more of a personal choice. If you have a large number of phone numbers stored lets say, numbers with no dash take up less space in the grand scheme of things I guess. Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie Question $2
Oh a few quick things. First, you can use substr to break up the phone instead of grabbing characters- might be a little easier to read long term. Secondly, mysql_real_escape_string will return the cleaned string, but doesn't change the original variable. So you'll need $phn = mysql_real_escape_string($phn); Thirdly anytime you use a single quote the strong is interpreted literally. You'll want to switch out the single quotes with double quotes, and then wrap $phn in single quotes in order to not break your query. Select ... Where phn = '$phn' I'd also really suggest looking at using PDO or even the mysqli extension tho instead of just plain mysql (believe this has been deprecated). Sorry for the quick reply, on mobile. But feel free to email me directly and I'll be happy to help out more. - Mike Sent from my iPhone On Jun 16, 2014, at 7:58 PM, Ethan Rosenberg erosenb...@hygeiabiomedical.com wrote: Dear List - I have the following code: The input from the form is a 10 digit string [1234567890] which is converted to phone number format [123-456-7890] $phn = $_POST[phone]; $phn = (string)$phn; $dsh = '-'; $Phn = $phn[0].$phn[1].$phn[2].$dsh.$phn[3].$phn[4].$phn[5].$dsh.$phn[6].$phn[7].$phn[8].$phn[9]; echo $Phn; // this is folded by Thunderbird. In the script, it is //all on one line mysql_real_escape_string($Phn); $sql1 ='select Lname, Fname from Customers where Phone = $Phn '; echo $sql1; //this always shows $phn as Phn and not as a numerical //string. $result1 = mysqli_query($cxn, $sql1); TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] newbie : For windows-target of PDO-DB-driver, CrossCompile or native-compile?
Hello, I need to write PHP-PDO database-driver/extension for a new database. The clarification I need: 1) Should I take the PHP-source on Windows-vista platform and build natively the DLL for win. Will the VisualStudio9 compile with all PHP-main src the extension driver to generate the target windows DLL (PDO extension). or 2) should I take PHP-src on Linux platform and use GNU Cross-compiler to generate DLL. Then transfer the DLL to the target-windowsVista PHP env. thanks, -sanjeev kumar
RE: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
-Original Message- From: Govinda [mailto:govinda.webdnat...@gmail.com] Sent: 05 August 2009 01:41 Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY `uniqueDate`; That's how I'd write it, anyway. Cheers! Mike -- Mike Ford, Electronic Information Developer, Libraries and Learning Innovation, Leeds Metropolitan University, C507, Civic Quarter Campus, Woodhouse Lane, LEEDS, LS1 3HE, United Kingdom Email: m.f...@leedsmet.ac.uk Tel: +44 113 812 4730 To view the terms under which this email is distributed, please go to http://disclaimer.leedsmet.ac.uk/email.htm -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: ... That's how I'd write it, anyway. I like to know your thinking; I'm working to learn to 'think in MySQL'.. so every input is appreciated. thanks! -G -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Me/this again. this works good: $query = SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`, date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing GROUP BY uniqueDate ORDER BY uniqueDate DESC LIMIT 62 UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm' AS `tableAlias` FROM aweber_7solar_confirm GROUP BY uniqueDate ORDER BY uniqueDate DESC LIMIT 62 UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY uniqueDate ORDER BY uniqueDate DESC LIMIT 62; except that I just added the ORDER BY clause onto each SELECT segment, and now I get this error: query failed: Incorrect usage of UNION and ORDER BY How can I order the results while still doing the UNION ALLs? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
except that I just added the ORDER BY clause onto each SELECT segment, and now I get this error: query failed: Incorrect usage of UNION and ORDER BY How can I order the results while still doing the UNION ALLs? You should only need one ORDER BY clause at the end of the whole query: (SELECT...) UNION ALL (SELECT...) UNION ALL (SELECT...) ORDER BY... I'm not sure if this syntax is portable to other SQL-based DBMSes, though. I'm certain that one system we use at work doesn't support it, but it's kind of a dinosaur so I don't like to draw too many conclusions from what it doesn't support. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Ford, Mike wrote: -Original Message- From: Govinda [mailto:govinda.webdnat...@gmail.com] Sent: 05 August 2009 01:41 Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY `uniqueDate`; That's a mysqlism :( It's not portable to other db's (apparently it's not part of the sql-spec). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date(DAY!)* in a timestamp column?
Just one other tiny point of style here: having given the expression date(solarAWDateTime) the alias uniqueDate, you should probably use that alias to refer to the same thing elsewhere in your query, such as in the GROUP BY column. So: [8] That's a mysqlism :( It's not portable to other db's (apparently it's not part of the sql-spec). I think I've even seen MySQL reject it in some cases. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
I should have given an example .. select count(*) as record_count, date(column_name) as date_field, 'my_table' as table_name union all select count(*) as record_count, date(column_name) as date_field, 'my_table_2' as table_name and end up with: count | date | table_name --- 5 | 2009-01-01 | table 1 10| 2009-01-01 | table 2 Ben, Chris, This is as far as I could get so far: $query = SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing UNION ALL SELECT count(*) AS confirm_count, date(solarAweberConfDateTime) AS solarAweberConfDate, 'aweber_7solar_confirmALIAS' AS aweber_7solar_confirm UNION ALL SELECT count(*) AS thankyou_count, date(solarAWDateTime) AS solarAWDate, 'aweber_7solar_awALIAS' AS aweber_7solar_aw; $foundUniqueDateROWS = mysql_query($query) or die(query failed: .mysql_error()); giving this error: query failed: Unknown column 'solarLandingDateTime' in 'field list' but I most certainly do have a column named 'solarLandingDateTime' in the table named 't7solar_landing'. So I am not sure what it's unhappy. ? John Butler (Govinda) govinda.webdnat...@gmail.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Niel, thanks (I was wondering about that... thought it seemed odd without a FROM clause) Now I have this: $query = SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM t7solar_landing GROUP BY DATE(solarLandingDateTime) UNION ALL SELECT count(*) AS confirm_count, date(solarAweberConfDateTime) AS solarAweberConfDate, 'aweber_7solar_confirmALIAS' AS aweber_7solar_confirm FROM aweber_7solar_confirm GROUP BY DATE(solarAweberConfDateTime) UNION ALL SELECT count(*) AS thankyou_count, date(solarAWDateTime) AS solarAWDate, 'aweber_7solar_awALIAS' AS aweber_7solar_aw FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) ; $foundUniqueDateROWS = mysql_query($query) or die(query failed: .mysql_error()); while ($uniqueDateROW = mysql_fetch_assoc($foundUniqueDateROWS)) { echo tr td.htmlentities($uniqueDateROW-solarLandingDate)/td td.htmlentities($uniqueDateROW-landing_count)./td td.htmlentities($uniqueDateROW-thankyou_count)./td td.(some math coming)./td td.htmlentities($uniqueDateROW-confirm_count)./td td.(some math coming)./td td.(some math coming)./td /tr; } Which seems to be outputting (I assume) the correct number of records (one for each unique date in each of the 3 tables, combined). But those table cells are coming out empty; i.e. $uniqueDateROW-solarLandingDate and $uniqueDateROW-landing_count are returning nothing. Can someone point me to understand why? I thought that: SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM t7solar_landing GROUP BY DATE(solarLandingDateTime) would, among other thing, assign the number of records counted in that first table to the alias 'landing_count'. No? I know it is annoying to hand-hold when newbies are this clueless. It's just hard for me to even know where to study when there are many blanks in my understanding. I am going to systematically plod through my books once I get them here. Meanwhile thanks to all who make time to help! -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Can someone point me to understand why? I thought that: SELECT COUNT(*) AS landing_count, date(solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing FROM t7solar_landing GROUP BY DATE(solarLandingDateTime) would, among other thing, assign the number of records counted in that first table to the alias 'landing_count'. No? Yes, but when you UNION that query with others it gets a bit more complicated. The UNION keyword adds more rows to the result-set, not more columns. So, when you join queries with a UNION, corresponding columns in each of those queries should all have the same aliases. So you probably don't want to say: SELECT nameABC AS `name1` FROM table1 UNION ALL SELECT nameDEF AS `name2` FROM table2 Here you're asking the DBMS to give you a result-set with just one column, in which the column is called `name1` some of the time, and `name2` the rest of the time. Doesn't make much sense, and MySQL will silently ignore one or the other of these aliases. Instead, say: SELECT nameABC AS `name` FROM table1 UNION ALL SELECT nameDEF AS `name` FROM table2 This will produce a result-set with just one column, called `name`. The number of rows in the result-set will equal the number of rows produced by the first SELECT, plus the number of rows produced by the second SELECT. Does that help make sense of why you need to add a second column to each query, with the name of the table? Like so: SELECT nameABC AS `name`, 'table1' AS `table_name` FROM table1 UNION ALL SELECT nameDEF AS `name`, 'table2' AS `table_name` FROM table2 This query will produce a result-set with two columns. The first column will be called `name` and the second will be called `table_name`; for example, supposing that table1 contains only boys' names and table2 contains only girls' names, you might get a result-set that includes these rows: name| table_name Robert | table1 James | table1 Lucy| table2 Teresa | table2 Then for each row, you would need to examine the value of the `table_name` column in PHP, to figure out which table the name is from. It looks like your current code is operating as though each row contains results from all three of your tables, which it doesn't. Each row only contains a result from one table. BTW, mysql_fetch_assoc() returns an array, not an object, so you'd need to use this syntax: $row['column'] As opposed to: $row-column If you prefer the latter syntax, you can use mysql_fetch_object(). Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Yes, but when you UNION that query with others it gets a bit more complicated ... If you prefer the latter syntax, you can use mysql_fetch_object(). Ben Bless you Ben!! That last post of yours led to my first decent dose of real significant SQL-syntax understanding since I started writing more than the *simplest* of SQL statements which I had gleaned from an intro PHP book. Your time will not go to waste! Thank you! I got it performing as expected. But there is one remaining thing that is mysterious to me. It will be easiest to explain/ask by showing my code and the result: this: - $query = SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`, date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing GROUP BY DATE(solarLandingDateTime) UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm' AS `tableAlias` FROM aweber_7solar_confirm GROUP BY DATE(solarAweberConfDateTime) UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) ; while { ... echo htmlentities($uniqueDateROW['uniqueDate']). .htmlentities($uniqueDateROW['CountUniqueDatesInMyTbl']). .htmlentities($uniqueDateROW['tableAlias']).br /\n; - returns this: 2009-07-28 5 t7solar_landing 2009-07-29 1 t7solar_landing 2009-08-02 2 t7solar_landing 2009-08-03 3 t7solar_landing 2009-08-04 2 t7solar_landing 2009-07-28 2 aweber_7solar_confirm 2009-07-29 1 aweber_7solar_confirm 2009-07-30 1 aweber_7solar_confirm 2009-07-31 1 aweber_7solar_confirm 2009-08-01 2 aweber_7solar_confirm 2009-08-02 1 aweber_7solar_confirm 2009-08-03 2 aweber_7solar_confirm 2009-08-04 1 aweber_7solar_confirm 2009-07-29 1 aweber_7solar_aw 2009-07-30 1 aweber_7solar_aw 2009-07-31 1 aweber_7solar_aw 2009-08-01 2 aweber_7solar_aw 2009-08-02 1 aweber_7solar_aw 2009-08-03 2 aweber_7solar_aw 2009-08-04 1 aweber_7solar_aw Now the first (date) and last (table) column are as expected. But what happened to count(*)?? There ARE 5 unique dates represented in the first table, as the highest returned value for count(*) shows, but as you can see, there are 8 unique dates in the second table, and 7 unique dates in the last table. Why then do we see only 1s and 2s in those columns for the latter 2 tables? And why does NOT the first table's rows include exclusively ONLY the values 1, 2, 3, 4, 5? I see two 2s and no 4. (??) I'm sure I can hack PHP in that while loop to generate the right count of unique dates for each table.. but I want to take advantage of this thread up to now and learn more SQL. The docs say count() Return[s] a count of the number of rows returned. In relation to that definition, I don't know what I am looking at in the results I got above. Can you 'splain me? John Butler (Govinda) govinda.webdnat...@gmail.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Aug 4, 2009, at 6:21 PM, Chris dmag...@gmail.com wrote: Govinda wrote: I should have given an example .. select count(*) as record_count, date(column_name) as date_field, 'my_table' as table_name union all select count(*) as record_count, date(column_name) as date_field, 'my_table_2' as table_name and end up with: count | date | table_name --- 5 | 2009-01-01 | table 1 10| 2009-01-01 | table 2 Ben, Chris, This is as far as I could get so far: $query = SELECT COUNT(*) AS landing_count, date (solarLandingDateTime) AS solarLandingDate, 't7solar_landingALIAS' AS t7solar_landing UNION ALL SELECT count(*) AS confirm_count, date (solarAweberConfDateTime) AS solarAweberConfDate, 'aweber_7solar_confirmALIAS' AS aweber_7solar_confirm UNION ALL SELECT count(*) AS thankyou_count, date(solarAWDateTime) AS solarAWDate, 'aweber_7solar_awALIAS' AS aweber_7solar_aw; $foundUniqueDateROWS = mysql_query($query) or die(query failed: .mysql_error()); giving this error: query failed: Unknown column 'solarLandingDateTime' in 'field list' but I most certainly do have a column named 'solarLandingDateTime' in the table named 't7solar_landing'. So I am not sure what it's unhappy. With a union the columns have to have the same name (or alias). Eg: SELECT COUNT(*) AS count, date(solarLandingDateTime) AS unique_date, 't7solar_landingALIAS' AS origin FROM tablename UNION ALL SELECT count(*) AS count, date(solarAweberConfDateTime) AS unique_date, 'aweber_7solar_confirmALIAS' AS origin FROM tablename etc -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Correction the column names will come form the first query, but they need to be the same datatype in those columns. So if the first query has a date datatype, the subsequent queries must have date columns in the first position Bastien Sent from my iPod -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
this: - $query = SELECT COUNT(*) AS `CountUniqueDatesInMyTbl`, date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS `tableAlias` FROM t7solar_landing GROUP BY DATE(solarLandingDateTime) UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm' AS `tableAlias` FROM aweber_7solar_confirm GROUP BY DATE(solarAweberConfDateTime) UNION ALL SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) ; returns this: 2009-07-28 5 t7solar_landing 2009-07-29 1 t7solar_landing 2009-08-02 2 t7solar_landing 2009-08-03 3 t7solar_landing 2009-08-04 2 t7solar_landing 2009-07-28 2 aweber_7solar_confirm 2009-07-29 1 aweber_7solar_confirm 2009-07-30 1 aweber_7solar_confirm 2009-07-31 1 aweber_7solar_confirm 2009-08-01 2 aweber_7solar_confirm 2009-08-02 1 aweber_7solar_confirm 2009-08-03 2 aweber_7solar_confirm 2009-08-04 1 aweber_7solar_confirm 2009-07-29 1 aweber_7solar_aw 2009-07-30 1 aweber_7solar_aw 2009-07-31 1 aweber_7solar_aw 2009-08-01 2 aweber_7solar_aw 2009-08-02 1 aweber_7solar_aw 2009-08-03 2 aweber_7solar_aw 2009-08-04 1 aweber_7solar_aw Now the first (date) and last (table) column are as expected. But what happened to count(*)?? There ARE 5 unique dates represented in the first table, as the highest returned value for count(*) shows, but as you can see, there are 8 unique dates in the second table, and 7 unique dates in the last table. Why then do we see only 1s and 2s in those columns for the latter 2 tables? And why does NOT the first table's rows include exclusively ONLY the values 1, 2, 3, 4, 5? I see two 2s and no 4. (??) Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) Your query says: get the date() from the solarAWDateTime field then group by that date and give me the date and number of results ie - you asked for the number of results per date - which is what you got. You didn't ask for the number of unique dates. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
With a union the columns have to have the same name (or alias). Eg: SELECT COUNT(*) AS count, date(solarLandingDateTime) AS unique_date, 't7solar_landingALIAS' AS origin FROM tablename UNION ALL SELECT count(*) AS count, date(solarAweberConfDateTime) AS unique_date, 'aweber_7solar_confirmALIAS' AS origin FROM tablename etc Correction the column names will come form the first query, but they need to be the same datatype in those columns. So if the first query has a date datatype, the subsequent queries must have date columns in the first position Thanks for the correction, I thought the column names/aliases had to match but you're right. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Taking this: SELECT count(*) AS `CountUniqueDatesInMyTbl`, date(solarAWDateTime) AS `uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY DATE(solarAWDateTime) Your query says: get the date() from the solarAWDateTime field then group by that date and give me the date and number of results ie - you asked for the number of results per date - which is what you got. You didn't ask for the number of unique dates. yes. And in fact that was what I wanted. I better quit for the day while I am ahead, and not spread around any more brain-fry now. Thank you Chris, and all. John Butler (Govinda) govinda.webdnat...@gmail.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Aug 3, 2009, at 12:29 AM, Govinda govinda.webdnat...@gmail.com wrote: Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. Niel, Bastien, thanks for your efforts to lead me to understanding this! I tried everything you both suggested. Ideally I would have some clear docs that outline the syntax for me, for such an example as I need.. and I would be able to check my code myself. Meanwhile, In every case, I just get every record in the table back as a result. So then I thought, try and make even a *simple* DISTINCT work, and then move on to the date thing... so I try this: //$foundTrackingRows=mysql_query(SELECT DISTINCT solarLandingDir, solarLandingIP, solarLandingDir, solarLandingDateTime FROM . $whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); In all the records in this table, there are only 3 possible values in the 'solarLandingDir' column (TINYTEXT): diysolar solar_hm (null) but I still get all the records back, with each distinct 'solarLandingDir' column value represented several times. So something really basic is missing in my understanding/code. Can you see what it is? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php The issue is with the select distinct, if you wrap the date in the parantheses for the with the distinct, the example I sent last night works fine. Select distinct ( date_format( solarLandingDate , '%Y-%m-%d')), solarLandingIP,... If you don't place the distinct parentheses around the date, the engines tries for a distinct on the entire row, which is why you end up with all rows Bastien Sent from my iPod -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Bastien, I had tried it with the parantheses around the date for the distinct. I tried again just now. Same result. But that's ok. I am onto the next step now. Niel, Jack, I got your fix working. It shows me that I am still so new; I own yet so little mastery of MySQL. Nisse, I see what you are suggesting. It seems I can go that route too. I have much to learn in every direction, so for right now anyway I am thinking to pursue the stream of thought started with what Niel and Jack just gave me. I do need data from the other columns too, and not just the date extracted from that timestamp field, ...and I need to count # of records in other tables that have the same unique date as the list of unique dates I just found in my first table, etc. .. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I need to play with everything before asking for more detailed help; I am just now asking if you think I am on the right track with my thinking - as I just mentioned in the sentence above this one? Thanks everyone! -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Govinda wrote: .. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I had to do something similar in code of my own a little while ago, and got some very good guidance on Stack Overflow. Here's the thread, you might find it helpful: http://stackoverflow.com/questions/946214/one-sql-query-or-many-in-a-loop The user whose answer is marked as the correct one (Quassnoi) also writes a helpful blog on SQL. You should be able to find the blog by clicking on the username. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Just keep in mind that while that may be a very possible solution when datasets are small. This could get problematic when for instance there are a 10 years worth of dates and millions of records in the other tables. The resulting program could end up taking lots of time to display data. In your case this might not happen if you do not get that much data, but again we do not know. Just something to keep in mind before deploying. Jack -Original Message- From: Govinda [mailto:govinda.webdnat...@gmail.com] Sent: Tuesday, August 04, 2009 12:34 AM To: php-db@lists.php.net Subject: Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column? Bastien, I had tried it with the parantheses around the date for the distinct. I tried again just now. Same result. But that's ok. I am onto the next step now. Niel, Jack, I got your fix working. It shows me that I am still so new; I own yet so little mastery of MySQL. Nisse, I see what you are suggesting. It seems I can go that route too. I have much to learn in every direction, so for right now anyway I am thinking to pursue the stream of thought started with what Niel and Jack just gave me. I do need data from the other columns too, and not just the date extracted from that timestamp field, ...and I need to count # of records in other tables that have the same unique date as the list of unique dates I just found in my first table, etc. .. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I need to play with everything before asking for more detailed help; I am just now asking if you think I am on the right track with my thinking - as I just mentioned in the sentence above this one? Thanks everyone! -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.392 / Virus Database: 270.13.40/2276 - Release Date: 08/01/09 18:04:00 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
.. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I had to do something similar in code of my own a little while ago, and got some very good guidance on Stack Overflow. Here's the thread, you might find it helpful: http://stackoverflow.com/questions/946214/one-sql-query-or-many-in-a-loop The user whose answer is marked as the correct one (Quassnoi) also writes a helpful blog on SQL. You should be able to find the blog by clicking on the username. Ben Thanks Ben. And yes Jack, ..I was attracted to the nested query as that required less new SQL ground to learn right now while I am expected to produce! But as that user (and others in that thread you gave, Ben) said, better to learn to do things the right way. So I need to read/learn more MySQL. Can you guys point me to where in the mysql docs I should be burying myself? Here's what I am trying to do: I have a table created by this: $SQL=CREATE TABLE t7solar_landing (solarLandingDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarLandingDir TINYTEXT,solarLandingIP TINYTEXT); and other tables too, like this: $SQL=CREATE TABLE aw_7solar_confirm (solarAwConfDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAwConfIP TINYTEXT); and this: $SQL=CREATE TABLE aw_7solar_aw (solarAWDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAWIP TINYTEXT,solarAWfm_email TINYTEXT,solarAWfm_meta_adtracking TINYTEXT, ... (plus more columns); I need to query these 3 tables (in one query! ;-) ...to return to me: one iteration of a while loop... ...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/ tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... ...*per unique DATE* found in the 'solarLandingDateTime' column of the 1st (t7solar_landing) table. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Govinda wrote: .. so my thought is to want to do nested query(ies), where: *within* the while loop of the first recordset (which is now successfully returning just rows with unique dates), I do other query(ies) which will (in their own code block) find all rows of the date we are iterating.. so I can, for example, count number of records for each unique date, do math/statistics, etc. I had to do something similar in code of my own a little while ago, and got some very good guidance on Stack Overflow. Here's the thread, you might find it helpful: http://stackoverflow.com/questions/946214/one-sql-query-or-many-in-a-loop The user whose answer is marked as the correct one (Quassnoi) also writes a helpful blog on SQL. You should be able to find the blog by clicking on the username. Ben Thanks Ben. And yes Jack, ..I was attracted to the nested query as that required less new SQL ground to learn right now while I am expected to produce! But as that user (and others in that thread you gave, Ben) said, better to learn to do things the right way. So I need to read/learn more MySQL. Can you guys point me to where in the mysql docs I should be burying myself? Here's what I am trying to do: I have a table created by this: $SQL=CREATE TABLE t7solar_landing (solarLandingDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarLandingDir TINYTEXT,solarLandingIP TINYTEXT); and other tables too, like this: $SQL=CREATE TABLE aw_7solar_confirm (solarAwConfDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAwConfIP TINYTEXT); and this: $SQL=CREATE TABLE aw_7solar_aw (solarAWDateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY,solarAWIP TINYTEXT,solarAWfm_email TINYTEXT,solarAWfm_meta_adtracking TINYTEXT, ... (plus more columns); I need to query these 3 tables (in one query! ;-) ...to return to me: one iteration of a while loop... ...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... ...*per unique DATE* found in the 'solarLandingDateTime' column of the 1st (t7solar_landing) table. What's the obsession with just doing one loop? To start off, do it in two steps, then worry about making it one statement (though it doesn't necessarily need to be done in one go). You need to get it right first before anything else. Get unique dates: $query = select DISTINCT DATE(solarLandingDateTime) AS landing_date from t7solar_landing; $result = mysql_query($query); while ($row = mysql_fetch_assoc($result)) { $date = $row['landing_date']; $query = select count(solarLandingDateTime) as landing_count, count(solarAwConfDateTime) as confirm_count, count(solarAWDateTime) as aw_count from t7solar_landing left join aw_7solar_confirm left join aw_7solar_aw where date(solarLandingDateTime) = '.mysql_real_escape_string($date).' or date(solarAwConfDateTime) = '.mysql_real_escape_string($date).' or date(solarAWDateTime) = '.mysql_real_escape_string($date).' ; // print results } After you're sure that you are getting the right results, work on doing it in one query. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... If you just need to count the records with a particular date you should be able to use this construction: SELECT COUNT(*) AS `record_count`, DATE(date_column) AS `date_field` FROM table GROUP BY `date_field` You could probably write a generalized PHP function (called 'build_query()' or something) that would construct this query given a table name and a date-column name, and call it once for each table/column pair. Then you could stitch the three query strings together, in PHP, into one large query using SQL's UNION ALL, which concatenates the results of multiple queries into one large result-set: (query 1) UNION ALL (query 2) UNION ALL (query 3) And then pass that one large query to the database. So I need to read/learn more MySQL. Can you guys point me to where in the mysql docs I should be burying myself? In my experience the MySQL manual isn't a great resource for learning SQL, at the level you're looking for. It's a fine reference if you already have a solid understanding of the basics. But to get that understanding, you might try the O'Reilly book called Learning SQL: http://oreilly.com/catalog/9780596520830/?CMP=AFC-ak_bookATT=Learning+SQL%2c+Second+Edition%2c Someone else here might know of some good online resources. I've not seen any, but then I haven't spent a whole lot of time looking. The parts of Learning SQL that I've seen are excellent. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Ben Dunlap wrote: ...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... If you just need to count the records with a particular date you should be able to use this construction: SELECT COUNT(*) AS `record_count`, DATE(date_column) AS `date_field` FROM table GROUP BY `date_field` You could probably write a generalized PHP function (called 'build_query()' or something) that would construct this query given a table name and a date-column name, and call it once for each table/column pair. Then you could stitch the three query strings together, in PHP, into one large query using SQL's UNION ALL, which concatenates the results of multiple queries into one large result-set: (query 1) UNION ALL (query 2) UNION ALL (query 3) And then pass that one large query to the database. .. and a field describing which table it came from, otherwise you end up with: count | date 5 | 2009-01-01 10| 2009-01-01 and no reference point. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Chris wrote: Ben Dunlap wrote: ...which will echo: trtd#records in 't7solar_landing' matching the given (iterating) date (in the 'solarLandingDateTime' column)/tdtd#records in 'aw_7solar_confirm' matching the given (iterating) date (in the 'solarAwConfDateTime' column)/tdtd#records in 'aw_7solar_aw' matching the given (iterating) date (in the 'solarAWDateTime' column)/td/tr... If you just need to count the records with a particular date you should be able to use this construction: SELECT COUNT(*) AS `record_count`, DATE(date_column) AS `date_field` FROM table GROUP BY `date_field` You could probably write a generalized PHP function (called 'build_query()' or something) that would construct this query given a table name and a date-column name, and call it once for each table/column pair. Then you could stitch the three query strings together, in PHP, into one large query using SQL's UNION ALL, which concatenates the results of multiple queries into one large result-set: (query 1) UNION ALL (query 2) UNION ALL (query 3) And then pass that one large query to the database. .. and a field describing which table it came from, otherwise you end up with: count | date 5 | 2009-01-01 10| 2009-01-01 and no reference point. I should have given an example .. select count(*) as record_count, date(column_name) as date_field, 'my_table' as table_name union all select count(*) as record_count, date(column_name) as date_field, 'my_table_2' as table_name and end up with: count | date | table_name --- 5 | 2009-01-01 | table 1 10| 2009-01-01 | table 2 -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Hi all I'm translating some code from another server-side language into PHP, and I need something that 'summarizes' results found from a MySQL SELECT. I.e. - $foundTrackingRows=mysql_query(SELECT... while ($TrackingRow = mysql_fetch_object($foundTrackingRows)) {... ..such that the while loop only loops *ONCE per unique _date_ found (regardless of the hour/min./sec.)* in my column which is of type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY For example, if I have column values like these: 2009-08-01 07:01:00 2009-07-30 18:16:37 2009-07-30 17:49:06 2009-07-27 17:35:52 2009-07-27 17:24:21 2009-07-27 17:23:03 ..then my while { loop would only fire 3 times. I do my RTFM; can someone just give me a good point in the right direction. Thanks! John Butler/(Govinda) govinda.webdnat...@gmail.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Govinda wrote: Hi all I'm translating some code from another server-side language into PHP, and I need something that 'summarizes' results found from a MySQL SELECT. I.e. - $foundTrackingRows=mysql_query(SELECT... while ($TrackingRow = mysql_fetch_object($foundTrackingRows)) {... ..such that the while loop only loops *ONCE per unique _date_ found (regardless of the hour/min./sec.)* in my column which is of type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP PRIMARY KEY For example, if I have column values like these: 2009-08-01 07:01:00 2009-07-30 18:16:37 2009-07-30 17:49:06 2009-07-27 17:35:52 2009-07-27 17:24:21 2009-07-27 17:23:03 ..then my while { loop would only fire 3 times. I do my RTFM; can someone just give me a good point in the right direction. Thanks! You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date() function in mysql on your timestamp column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date without knowing your original query it's hard to give an example, but: select distinct date(timestamp_column) from table; -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date() function in mysql on your timestamp column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date without knowing your original query it's hard to give an example, but: select distinct date(timestamp_column) from table; Thanks Chris, I am getting closer, but so far it is not iterating only once per unique 'date part of the datetime expression', it is returning all the rows in the table, including those with the very same date but different time in the value of the 'solarLandingDateTime' column. There is not alot of discussion in the mysql docs that I saw about how to work with DISTINCT. I need to grab data out of the 3 columns: solarLandingIP, solarLandingDir, solarLandingDateTime (this part of my SELECT is working). This is what I have: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); -Govinda
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Sun, Aug 2, 2009 at 8:02 PM, Govindagovinda.webdnat...@gmail.com wrote: You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date() function in mysql on your timestamp column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date without knowing your original query it's hard to give an example, but: select distinct date(timestamp_column) from table; Thanks Chris, I am getting closer, but so far it is not iterating only once per unique 'date part of the datetime expression', it is returning all the rows in the table, including those with the very same date but different time in the value of the 'solarLandingDateTime' column. There is not alot of discussion in the mysql docs that I saw about how to work with DISTINCT. I need to grab data out of the 3 columns: solarLandingIP, solarLandingDir, solarLandingDateTime (this part of my SELECT is working). This is what I have: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); -Govinda try foundTrackingRows=mysql_query(SELECT DISTINCT DATE_FORMAT('%Y-%m,%d',solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Sun, Aug 2, 2009 at 8:02 PM, Govindagovinda.webdnat...@gmail.com wrote: You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date() function in mysql on your timestamp column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date without knowing your original query it's hard to give an example, but: select distinct date(timestamp_column) from table; Thanks Chris, I am getting closer, but so far it is not iterating only once per unique 'date part of the datetime expression', it is returning all the rows in the table, including those with the very same date but different time in the value of the 'solarLandingDateTime' column. There is not alot of discussion in the mysql docs that I saw about how to work with DISTINCT. I need to grab data out of the 3 columns: solarLandingIP, solarLandingDir, solarLandingDateTime (this part of my SELECT is working). This is what I have: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM . $whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); -Govinda try foundTrackingRows=mysql_query(SELECT DISTINCT DATE_FORMAT('%Y-%m,%d',solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM . $whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); Bastien, I just did try that. I also tried this, (thinking there was a typo in your version, in the DATE_FORMAT formatting string), : $foundTrackingRows=mysql_query(SELECT DISTINCT DATE_FORMAT('%Y-%m- %d',solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); still no luck. -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
What does the resultset look like? I am getting every record in the table back
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
On Sun, Aug 2, 2009 at 11:13 PM, Govindagovinda.webdnat...@gmail.com wrote: What does the resultset look like? I am getting every record in the table back my bad, i reversed the mysql and php date function formats try SELECT distinct( DATE_FORMAT( fieldname, '%Y-%m-%d' ) ) FROM table ORDER BY field DESC -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date() function in mysql on your timestamp column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date without knowing your original query it's hard to give an example, but: select distinct date(timestamp_column) from table; Thanks Chris, I am getting closer, but so far it is not iterating only once per unique 'date part of the datetime expression', it is returning all the rows in the table, including those with the very same date but different time in the value of the 'solarLandingDateTime' column. There is not alot of discussion in the mysql docs that I saw about how to work with DISTINCT. I need to grab data out of the 3 columns: solarLandingIP, solarLandingDir, solarLandingDateTime (this part of my SELECT is working). This is what I have: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); -Govinda There is no comma between DATE(solarLandingDateTime) and solarLandingIP which means the DATE column will use the alias 'solarLandingIP'. Is this your intention? Or is the solarLandingIP another column from the table. If the latter, you may want to do something like this: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) AS solarLandingDate, solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: . mysql_error()); If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
You need to do this on the mysql side, not in php - php can't summarize the data before processing it, so you need to use something like the date() function in mysql on your timestamp column. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date without knowing your original query it's hard to give an example, but: select distinct date(timestamp_column) from table; Thanks Chris, I am getting closer, but so far it is not iterating only once per unique 'date part of the datetime expression', it is returning all the rows in the table, including those with the very same date but different time in the value of the 'solarLandingDateTime' column. There is not alot of discussion in the mysql docs that I saw about how to work with DISTINCT. I need to grab data out of the 3 columns: solarLandingIP, solarLandingDir, solarLandingDateTime (this part of my SELECT is working). This is what I have: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); -Govinda There is no comma between DATE(solarLandingDateTime) and solarLandingIP which means the DATE column will use the alias 'solarLandingIP'. Is this your intention? Or is the solarLandingIP another column from the table. If the latter, you may want to do something like this: $foundTrackingRows=mysql_query(SELECT DISTINCT DATE(solarLandingDateTime) AS solarLandingDate, solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: . mysql_error()); Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. Niel, Bastien, thanks for your efforts to lead me to understanding this! I tried everything you both suggested. Ideally I would have some clear docs that outline the syntax for me, for such an example as I need.. and I would be able to check my code myself. Meanwhile, In every case, I just get every record in the table back as a result. So then I thought, try and make even a *simple* DISTINCT work, and then move on to the date thing... so I try this: //$foundTrackingRows=mysql_query(SELECT DISTINCT solarLandingDir, solarLandingIP, solarLandingDir, solarLandingDateTime FROM . $whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); In all the records in this table, there are only 3 possible values in the 'solarLandingDir' column (TINYTEXT): diysolar solar_hm (null) but I still get all the records back, with each distinct 'solarLandingDir' column value represented several times. So something really basic is missing in my understanding/code. Can you see what it is? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
The distinct can only work as you want it to work when you only select the date column and only the date part (not the time part). Unfortunately I'm more an oracle DBA where the date functions are more clear to me so can not help with exact syntax. I think what you should be doing is returning the entire set of records (all required columns) in sorted by date order and loop through all of them. keep track of the last date processed and if it is the same as the current record, process nothing and go get the next record. BUT I am not quite sure what you are trying to achieve so my advise may be completely flawed. Jack 2009/8/3 Govinda govinda.webdnat...@gmail.com Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. Niel, Bastien, thanks for your efforts to lead me to understanding this! I tried everything you both suggested. Ideally I would have some clear docs that outline the syntax for me, for such an example as I need.. and I would be able to check my code myself. Meanwhile, In every case, I just get every record in the table back as a result. So then I thought, try and make even a *simple* DISTINCT work, and then move on to the date thing... so I try this: //$foundTrackingRows=mysql_query(SELECT DISTINCT solarLandingDir, solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); In all the records in this table, there are only 3 possible values in the 'solarLandingDir' column (TINYTEXT): diysolar solar_hm (null) but I still get all the records back, with each distinct 'solarLandingDir' column value represented several times. So something really basic is missing in my understanding/code. Can you see what it is? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Jack van Zanen - This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation
Re: [PHP-DB] newbie: how to return one iteration *per unique date (DAY!)* in a timestamp column?
The distinct can only work as you want it to work when you only select the date column and only the date part (not the time part). Unfortunately I'm more an oracle DBA where the date functions are more clear to me so can not help with exact syntax. I think what you should be doing is returning the entire set of records (all required columns) in sorted by date order and loop through all of them. keep track of the last date processed and if it is the same as the current record, process nothing and go get the next record. BUT I am not quite sure what you are trying to achieve so my advise may be completely flawed. Jack 2009/8/3 Govinda govinda.webdnat...@gmail.com Doh, should have realised this before, but it's after 4 AM here. Jack is absolutely correct here, you are getting all the rows back because that is what the query asks for. Try this as your first step SELECT DISTINCT DATE(solarLandingDateTime) AS solarLandingDate FROM . $whichTable . ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: . mysql_error()); This should get your unique rows by date. The previous queries used ALL of the column names to form a unique row, and that made all the rows DISTINCT. Give us an idea of your table structure, if possible post the CREATE statement for it. Oops, forgot to mention that with the alias you can change the ORDER BY clause to use the aliased column data: ORDER BY solarLandingDate DESC this will only use the returned data instead of the entire column. If you are aliasing a column it is better to use the optional AS keyword to avoid confusion. MySQL's DATE function returns dates formatted as '-MM-DD' so DATE_FORMAT is not needed here. Niel, Bastien, thanks for your efforts to lead me to understanding this! I tried everything you both suggested. Ideally I would have some clear docs that outline the syntax for me, for such an example as I need.. and I would be able to check my code myself. Meanwhile, In every case, I just get every record in the table back as a result. So then I thought, try and make even a *simple* DISTINCT work, and then move on to the date thing... so I try this: //$foundTrackingRows=mysql_query(SELECT DISTINCT solarLandingDir, solarLandingIP, solarLandingDir, solarLandingDateTime FROM .$whichTable. ORDER BY solarLandingDateTime DESC LIMIT $Maxrecs2Show) or die(query failed: .mysql_error()); In all the records in this table, there are only 3 possible values in the 'solarLandingDir' column (TINYTEXT): diysolar solar_hm (null) but I still get all the records back, with each distinct 'solarLandingDir' column value represented several times. So something really basic is missing in my understanding/code. Can you see what it is? -Govinda -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Jack van Zanen - This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie question on MDB2 error handling
Hello all, I am quite new to php and especially MDB2 package. Despite some thorough research I could not find an answer yet. When I try to connect to a database using MDB2::connect method, and the database server is not running, I only get an error code of -1 and a message saying MDB2 Error: unknown error. Looking at MDB2 source it rather should say connect failed with a code of -24 (MDB2_ERROR_CONNECT_FAILED). Is my expectation wrong, or how can I get some meaningful message and error code in this situation? The code I use is: $dsn = mysqli://mysql:my...@127.0.0.1/MYDB; // This works when mysql server is running $con = MDB2::connect($dsn); if(MDB2::isError($con)) { print_r($con-getCode()); print_r($con-getMessage()); } Any hint is warmly appreciated! Thanks, Pascal. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie help
I'd like to apologize to both of you, this is my fault as i did not really clear what my requirements are . basically it will be for my VoIP system, Customer A (which is me) will have customers that may also be a reseller, so this is where Cust 1 will have Cust B. Each customer will have a login name, that they can use to check cdr's, view their rates, create rates for their customers (if customer is a reseller, so that they can top-up whatever rate was assigned to it by its parent) billing (sending invoice via e-mail) and trouble ticket. that's what is in the plan for now. Thank you Regards, Nhadie TG wrote: Nice blanket statements, Bob. What, exactly, is wrong with what I'm describing? What I've tried to do, without having more information about the type of data and how he intends to use it, is give a high level idea of things that could be tried. There's no way I can give an exact breakdown of exactly how to configure anything without more information. All I did was outline some general schemes that COULD be used on a high level. Not trying to start a fight, but your message is borderline trolling (and of course I'm responding... falling for it.. but I assume it wasn't meant as a troll post). It's not the BEST advice I could give because I don't have all the information. Not saying I'm the smartest person on this list, but when someone is looking for ideas, some ideas to try.. so you can grow and learn at the very least.. are better than none. If a better idea comes along, all the better. Hopefully the better solution will get posted to the list. I'm not sure anything I said is as backwards as you make it out to be. So let's keep it constructive. If you can point out clearly what I said that's terribly wrong and give a better solution, please enlighten. Always willing to learn new things if they are, indeed, better. -TG - Original Message - From: Bob Chatman [EMAIL PROTECTED] To: TG [EMAIL PROTECTED] Cc: nhadie [EMAIL PROTECTED], php-db@lists.php.net Date: Fri, 28 Sep 2007 23:23:41 -0700 Subject: Re: [PHP-DB] newbie help This is definitely not the best advice in the world. The truth is you could staple your eye lids open and never blink, but it wouldn't be very beneficial. I highly suggest you invest some of your time in reading about databases and how relational databases work, and more than that how they work well, instead of trying to create some monster that will end up causing you more stress than income. I would also suggest you sit down and try to plan out whatever the hell you are trying to do before you get too crazy. If you code like you describe things here you are bound to confuse variable names and thats just crazy. Try to clean up your idea and ill gladly direct you to some of your options. and most of all, dont try to do this as TG has described here. On 9/28/07, TG [EMAIL PROTECTED] wrote: How resource intensive it is depends a lot on how much data you have and what you're doing with it. You want to reduce the number of connections and disconnects, since those are really intensive, but if everything is on the same server, you can do cross database queries. As for the table size exceeding the OS limit, that'd take quite a bit of data to do. With proper database design, that shouldn't be too much of an issue. If you have 2+gb of data in a single table, that's one monster table. Some info here about table size restrictions for MySQL: http://dev.mysql.com/doc/refman/4.1/en/full-table.html You could create a table and stuff it full of thousands of records and try to check the physical file size of that table's file and see how tricky it would be to actually 'fill' a table. Ideally, for your 'customer' table would contain just the data that was unique to that customer. Typically data that all your customers are going to have a single entry for so you don't have a lot of empty spots. Anything that you have multiple entries for or are unique to a single customer, you could put in another table as meta data (additional information that didn't fit in the main table). You probably want to ignore my original parent/child threading idea. I was just illustrating a way that you COULD do it. What you may want to do is have a 'customers' table, which contain your customers, then another table for the base information for your customer's customers with an ID field that matches the entry in your 'customers' table. For additional tables in the database, you could do something like you meantioned... have custname1_tablename1 and use a code in your customers table to indicate the table prefix. This kind of thing is used a lot in systems like phpBB, Gallery, etc so you can have multiple copies installed on the same server. It'd be the same idea when dealing with multiple customers. Just make sure that the prefix you use for a customer doesn't use characters that are bad for table names. What kind of data are you
RE: [PHP-DB] newbie help
This may be overkill for what you're doing, but based upon your layout, you might consider a nested set. Since you're a newb, it may be too complex to code: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html This is how you setup products and categories for example I think your solution is much simpler and could simply use a few glue tables and some flags so you know which table to look in (reseller, customer, etc) -Original Message- From: nhadie [mailto:[EMAIL PROTECTED] Sent: Thursday, September 27, 2007 8:31 PM To: php-db@lists.php.net Subject: [PHP-DB] newbie help Hi All, I would like to setup a system something like this customer A /\ cust 1cust 2 / \ \ cust B cust C cust D customer A has customer 1 and 2, cust 1 has cust B and C etc, etc. what i'm after is when a customer user logs in he can only see record of its customer, e.g cust A logs in, it can only see records of cust 1 and cust 2, if cust 2 is login it can only see cust D and so on how would i approach this? should every customer have it's own database, consisting of all tables i need e.g customer detail table, and i will use the database name as session variable, so that when they add records it be inserted on table under that database? sorry if my questions are confusing, hope anyone can help me, i just need help on how to approach it at the beginning so it will not be messy later on. Thank you. Regards, Nhadie -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie help
This is definitely not the best advice in the world. The truth is you could staple your eye lids open and never blink, but it wouldn't be very beneficial. I highly suggest you invest some of your time in reading about databases and how relational databases work, and more than that how they work well, instead of trying to create some monster that will end up causing you more stress than income. I would also suggest you sit down and try to plan out whatever the hell you are trying to do before you get too crazy. If you code like you describe things here you are bound to confuse variable names and thats just crazy. Try to clean up your idea and ill gladly direct you to some of your options. and most of all, dont try to do this as TG has described here. On 9/28/07, TG [EMAIL PROTECTED] wrote: How resource intensive it is depends a lot on how much data you have and what you're doing with it. You want to reduce the number of connections and disconnects, since those are really intensive, but if everything is on the same server, you can do cross database queries. As for the table size exceeding the OS limit, that'd take quite a bit of data to do. With proper database design, that shouldn't be too much of an issue. If you have 2+gb of data in a single table, that's one monster table. Some info here about table size restrictions for MySQL: http://dev.mysql.com/doc/refman/4.1/en/full-table.html You could create a table and stuff it full of thousands of records and try to check the physical file size of that table's file and see how tricky it would be to actually 'fill' a table. Ideally, for your 'customer' table would contain just the data that was unique to that customer. Typically data that all your customers are going to have a single entry for so you don't have a lot of empty spots. Anything that you have multiple entries for or are unique to a single customer, you could put in another table as meta data (additional information that didn't fit in the main table). You probably want to ignore my original parent/child threading idea. I was just illustrating a way that you COULD do it. What you may want to do is have a 'customers' table, which contain your customers, then another table for the base information for your customer's customers with an ID field that matches the entry in your 'customers' table. For additional tables in the database, you could do something like you meantioned... have custname1_tablename1 and use a code in your customers table to indicate the table prefix. This kind of thing is used a lot in systems like phpBB, Gallery, etc so you can have multiple copies installed on the same server. It'd be the same idea when dealing with multiple customers. Just make sure that the prefix you use for a customer doesn't use characters that are bad for table names. What kind of data are you thinking about storing for your customers and your customer's customers? And what other data do you need to store for whatever your webapp does? -TG - Original Message - From: nhadie [EMAIL PROTECTED] To: php-db@lists.php.net Date: Fri, 28 Sep 2007 23:49:23 +0800 Subject: Re: [PHP-DB] newbie help Thanks for your reply, which solution would be faster and not put too much load on the resources of the server? single or multiple database? If i use a single database, would i be having same tables for each customer, e.g. if i have customerinfo table for customers of A, would i also have customerinfo table on customer 1 and 2 and so on, but changing the name by prefixing something to the table name e.g cust1_customerinfo. i'm thinking if i use only a single table for, then i might have problems on the file size limit of the OS, would i have that problem? thanks again for your help, i really,really appreciate it. regards, nhadie TG wrote: You could do separate databases if you want. It all depends on how you want to organize your data (keeping in mind data backup strategies and stuff). Using separate databases on the same server, you might have another database for the 'common' data. Any variables you store in the database that don't really change and are common to all databases. Or if you log anything and want to keep the log data in one central location, you could put it in the 'common' database. If you wanted to store all the data in one database, you could use something like forum threading techniques to show a parent/child relationship for the customers. id nameparent 1 customerANULL 2 cust11 3 cust21 4 custB 2 5 custC 2 6 custD 3 Then it's just a matter of drilling down, collecting a list of ID's for that customer and the sub-customers
Re: [PHP-DB] newbie help
Nice blanket statements, Bob. What, exactly, is wrong with what I'm describing? What I've tried to do, without having more information about the type of data and how he intends to use it, is give a high level idea of things that could be tried. There's no way I can give an exact breakdown of exactly how to configure anything without more information. All I did was outline some general schemes that COULD be used on a high level. Not trying to start a fight, but your message is borderline trolling (and of course I'm responding... falling for it.. but I assume it wasn't meant as a troll post). It's not the BEST advice I could give because I don't have all the information. Not saying I'm the smartest person on this list, but when someone is looking for ideas, some ideas to try.. so you can grow and learn at the very least.. are better than none. If a better idea comes along, all the better. Hopefully the better solution will get posted to the list. I'm not sure anything I said is as backwards as you make it out to be. So let's keep it constructive. If you can point out clearly what I said that's terribly wrong and give a better solution, please enlighten. Always willing to learn new things if they are, indeed, better. -TG - Original Message - From: Bob Chatman [EMAIL PROTECTED] To: TG [EMAIL PROTECTED] Cc: nhadie [EMAIL PROTECTED], php-db@lists.php.net Date: Fri, 28 Sep 2007 23:23:41 -0700 Subject: Re: [PHP-DB] newbie help This is definitely not the best advice in the world. The truth is you could staple your eye lids open and never blink, but it wouldn't be very beneficial. I highly suggest you invest some of your time in reading about databases and how relational databases work, and more than that how they work well, instead of trying to create some monster that will end up causing you more stress than income. I would also suggest you sit down and try to plan out whatever the hell you are trying to do before you get too crazy. If you code like you describe things here you are bound to confuse variable names and thats just crazy. Try to clean up your idea and ill gladly direct you to some of your options. and most of all, dont try to do this as TG has described here. On 9/28/07, TG [EMAIL PROTECTED] wrote: How resource intensive it is depends a lot on how much data you have and what you're doing with it. You want to reduce the number of connections and disconnects, since those are really intensive, but if everything is on the same server, you can do cross database queries. As for the table size exceeding the OS limit, that'd take quite a bit of data to do. With proper database design, that shouldn't be too much of an issue. If you have 2+gb of data in a single table, that's one monster table. Some info here about table size restrictions for MySQL: http://dev.mysql.com/doc/refman/4.1/en/full-table.html You could create a table and stuff it full of thousands of records and try to check the physical file size of that table's file and see how tricky it would be to actually 'fill' a table. Ideally, for your 'customer' table would contain just the data that was unique to that customer. Typically data that all your customers are going to have a single entry for so you don't have a lot of empty spots. Anything that you have multiple entries for or are unique to a single customer, you could put in another table as meta data (additional information that didn't fit in the main table). You probably want to ignore my original parent/child threading idea. I was just illustrating a way that you COULD do it. What you may want to do is have a 'customers' table, which contain your customers, then another table for the base information for your customer's customers with an ID field that matches the entry in your 'customers' table. For additional tables in the database, you could do something like you meantioned... have custname1_tablename1 and use a code in your customers table to indicate the table prefix. This kind of thing is used a lot in systems like phpBB, Gallery, etc so you can have multiple copies installed on the same server. It'd be the same idea when dealing with multiple customers. Just make sure that the prefix you use for a customer doesn't use characters that are bad for table names. What kind of data are you thinking about storing for your customers and your customer's customers? And what other data do you need to store for whatever your webapp does? -TG - Original Message - From: nhadie [EMAIL PROTECTED] To: php-db@lists.php.net Date: Fri, 28 Sep 2007 23:49:23 +0800 Subject: Re: [PHP-DB] newbie help Thanks for your reply, which solution would be faster and not put too much load on the resources of the server? single or multiple database? If i use a single database, would i be having same tables
Re: [PHP-DB] newbie help
Thanks for your reply, which solution would be faster and not put too much load on the resources of the server? single or multiple database? If i use a single database, would i be having same tables for each customer, e.g. if i have customerinfo table for customers of A, would i also have customerinfo table on customer 1 and 2 and so on, but changing the name by prefixing something to the table name e.g cust1_customerinfo. i'm thinking if i use only a single table for, then i might have problems on the file size limit of the OS, would i have that problem? thanks again for your help, i really,really appreciate it. regards, nhadie TG wrote: You could do separate databases if you want. It all depends on how you want to organize your data (keeping in mind data backup strategies and stuff). Using separate databases on the same server, you might have another database for the 'common' data. Any variables you store in the database that don't really change and are common to all databases. Or if you log anything and want to keep the log data in one central location, you could put it in the 'common' database. If you wanted to store all the data in one database, you could use something like forum threading techniques to show a parent/child relationship for the customers. id nameparent 1 customerANULL 2 cust11 3 cust21 4 custB 2 5 custC 2 6 custD 3 Then it's just a matter of drilling down, collecting a list of ID's for that customer and the sub-customers. Lotsof ways to tackle this problem. -TG - Original Message - From: nhadie [EMAIL PROTECTED] To: php-db@lists.php.net Date: Fri, 28 Sep 2007 11:30:42 +0800 Subject: [PHP-DB] newbie help Hi All, I would like to setup a system something like this customer A /\ cust 1cust 2 / \ \ cust B cust C cust D customer A has customer 1 and 2, cust 1 has cust B and C etc, etc. what i'm after is when a customer user logs in he can only see record of its customer, e.g cust A logs in, it can only see records of cust 1 and cust 2, if cust 2 is login it can only see cust D and so on how would i approach this? should every customer have it's own database, consisting of all tables i need e.g customer detail table, and i will use the database name as session variable, so that when they add records it be inserted on table under that database? sorry if my questions are confusing, hope anyone can help me, i just need help on how to approach it at the beginning so it will not be messy later on. Thank you. Regards, Nhadie -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie help
How resource intensive it is depends a lot on how much data you have and what you're doing with it. You want to reduce the number of connections and disconnects, since those are really intensive, but if everything is on the same server, you can do cross database queries. As for the table size exceeding the OS limit, that'd take quite a bit of data to do. With proper database design, that shouldn't be too much of an issue. If you have 2+gb of data in a single table, that's one monster table. Some info here about table size restrictions for MySQL: http://dev.mysql.com/doc/refman/4.1/en/full-table.html You could create a table and stuff it full of thousands of records and try to check the physical file size of that table's file and see how tricky it would be to actually 'fill' a table. Ideally, for your 'customer' table would contain just the data that was unique to that customer. Typically data that all your customers are going to have a single entry for so you don't have a lot of empty spots. Anything that you have multiple entries for or are unique to a single customer, you could put in another table as meta data (additional information that didn't fit in the main table). You probably want to ignore my original parent/child threading idea. I was just illustrating a way that you COULD do it. What you may want to do is have a 'customers' table, which contain your customers, then another table for the base information for your customer's customers with an ID field that matches the entry in your 'customers' table. For additional tables in the database, you could do something like you meantioned... have custname1_tablename1 and use a code in your customers table to indicate the table prefix. This kind of thing is used a lot in systems like phpBB, Gallery, etc so you can have multiple copies installed on the same server. It'd be the same idea when dealing with multiple customers. Just make sure that the prefix you use for a customer doesn't use characters that are bad for table names. What kind of data are you thinking about storing for your customers and your customer's customers? And what other data do you need to store for whatever your webapp does? -TG - Original Message - From: nhadie [EMAIL PROTECTED] To: php-db@lists.php.net Date: Fri, 28 Sep 2007 23:49:23 +0800 Subject: Re: [PHP-DB] newbie help Thanks for your reply, which solution would be faster and not put too much load on the resources of the server? single or multiple database? If i use a single database, would i be having same tables for each customer, e.g. if i have customerinfo table for customers of A, would i also have customerinfo table on customer 1 and 2 and so on, but changing the name by prefixing something to the table name e.g cust1_customerinfo. i'm thinking if i use only a single table for, then i might have problems on the file size limit of the OS, would i have that problem? thanks again for your help, i really,really appreciate it. regards, nhadie TG wrote: You could do separate databases if you want. It all depends on how you want to organize your data (keeping in mind data backup strategies and stuff). Using separate databases on the same server, you might have another database for the 'common' data. Any variables you store in the database that don't really change and are common to all databases. Or if you log anything and want to keep the log data in one central location, you could put it in the 'common' database. If you wanted to store all the data in one database, you could use something like forum threading techniques to show a parent/child relationship for the customers. id nameparent 1 customerANULL 2 cust11 3 cust21 4 custB 2 5 custC 2 6 custD 3 Then it's just a matter of drilling down, collecting a list of ID's for that customer and the sub-customers. Lotsof ways to tackle this problem. -TG - Original Message - From: nhadie [EMAIL PROTECTED] To: php-db@lists.php.net Date: Fri, 28 Sep 2007 11:30:42 +0800 Subject: [PHP-DB] newbie help Hi All, I would like to setup a system something like this customer A /\ cust 1cust 2 / \ \ cust B cust C cust D customer A has customer 1
[PHP-DB] newbie help
Hi All, I would like to setup a system something like this customer A /\ cust 1cust 2 / \ \ cust B cust C cust D customer A has customer 1 and 2, cust 1 has cust B and C etc, etc. what i'm after is when a customer user logs in he can only see record of its customer, e.g cust A logs in, it can only see records of cust 1 and cust 2, if cust 2 is login it can only see cust D and so on how would i approach this? should every customer have it's own database, consisting of all tables i need e.g customer detail table, and i will use the database name as session variable, so that when they add records it be inserted on table under that database? sorry if my questions are confusing, hope anyone can help me, i just need help on how to approach it at the beginning so it will not be messy later on. Thank you. Regards, Nhadie -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie alert: supplied argument is not a valid MySQL result resource
Hi On my way to bed so too tired too look closely, but at a guess I'd say it's a logic error. I would guess this: $sid = mysql_fetch_array($query) || die ('Could not fetch from database: ' . mysql_error()); // --- line 49 isn't evaluating in the order you expect.. You're using a logic statement as an assignment, and the result of that get's assigned to your variable not the resource you expect. Try it like this to see if it works: $sid = mysql_fetch_array($query); if ($sid === false) die ('Could not fetch from database: ' . mysql_error()); If that's not the problem, I haven't got a clue until after sleep and coffee. I've had some sleep, and coffee, and I see I missed something silly. I should of suggested this // get sid and write cookies $query = mysql_query(SELECT MAX(SID) FROM Sessions); if ($query === false) die ('Could not query database: ' . mysql_error()); $sid = mysql_fetch_array($query) if ($sid === false) die ('Could not fetch from database: ' . mysql_error()); -- Niel Archer -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie alert: supplied argument is not a valid MySQL result resource
lameck kassana [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] hey just make this $sid = mysql_fetch_array($query) or die ('Could not fetch from database: ' . mysql_error().); try this Yep. I figured that out yesterday. Hit the nail right on the head. As I've learned from experimentation, the pipe operator takes precedence to the assignment operator so I was basically evaluating whether my query or a die statement returned a true value (which the query statement was, so it never processed the die()) and then assigning true, or 1 to $query. The 'or' operator by contrast processes after the assignment, so the following line: $query = mysql_query(some SQL) or die(); first assigns a handle to $query (assuming the query is valid, which in my case it was) and then evaluated the value of query. This leaves me with two remaining questions. I'm coming from a Perl background, so I'm wondering if there's a documentation page somewhere for PHP operators (something along the likes of perlop) and as an experiment I tried the following code, and it behaved exactly like the code I first posted, which I'm not sure why: ($query = mysql_query(valid SQL)) || die(); Shouldn't everything within the first set of parentheses here evaluate as the first || condition? Thanks for everyone's help, Matt -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie PHP/MySQL question
yes. Note the delete from canton query. I found something. case.. Somehow, mysql treat A the same as a Maybe from a previous query? I mean, did you make sure the table was clear before starting the script? $target-query(delete from canton); for ($ii=0; $ii256; $ii++) { for ($jj=0; $jj256; $jj++) { echo $ii ... $jj . \n; $query=insert into canton ( big5 ) values ( ' . mysql_real_escape_string(chr($ii).chr($jj)) . ' ); $target-query($query); } }
[PHP-DB] newbie PHP/MySQL question
A table with a column big5 char(2) not null primary key. $target-query(delete from canton); for ($ii=0; $ii256; $ii++) { for ($jj=0; $jj256; $jj++) { echo $ii ... $jj . \n; $query=insert into canton ( big5 ) values ( ' . mysql_real_escape_string(chr($ii).chr($jj)) . ' ); $target-query($query); } } The program died with this output: 0.92 0.93 0.94 0.95 0.96 0.97 Duplicate entry '' for key 1 The character strings are unique. Why did it find a duplicate at (0,97)?
Re: [PHP-DB] newbie PHP/MySQL question
Maybe from a previous query? I mean, did you make sure the table was clear before starting the script? On Monday 02 January 2006 6:18 pm, toylet wrote: A table with a column big5 char(2) not null primary key. $target-query(delete from canton); for ($ii=0; $ii256; $ii++) { for ($jj=0; $jj256; $jj++) { echo $ii ... $jj . \n; $query=insert into canton ( big5 ) values ( ' . mysql_real_escape_string(chr($ii).chr($jj)) . ' ); $target-query($query); } } The program died with this output: 0.92 0.93 0.94 0.95 0.96 0.97 Duplicate entry '' for key 1 The character strings are unique. Why did it find a duplicate at (0,97)? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] newbie question on PHP Mysql...
Hi all! I'm taking my first steps with PHP MySQL. Can anyone give me a hint on why this would not work? * $result = mysql_query('SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets) FROM radacct WHERE username = $argv[1] '); echo mysql_result($result,0), \n; echo mysql_result($result,0,1); * I get: Warning: mysql_result(): supplied argument is not a valid MySQL result resource Regards, Evert -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie with mail problem
Use a real hostname, not 'localhost'.- -- Atte, Andrés G. Montañez Técnico en Redes y Telecomunicaciones Montevideo - Uruguay -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie with mail problem
Andrés G. Montañez [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Use a real hostname, not 'localhost'.- Will give that a try. Thank you. -- Atte, Andrés G. Montañez Técnico en Redes y Telecomunicaciones Montevideo - Uruguay -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie with mail problem
I've installed a game on my server, but it wants to email new registrants. When it attempts this the following error is displayed: Warning: mail() [function.mail]: Failed to connect to mailserver at localhost port 25, verify your SMTP and smtp_port setting in php.ini or use ini_set() in C:\XAMPP\xampplite\htdocs\nitefly\dragon\dk1110\users.php on line 239. The relevant parts in php.ini may be: [mail function] ; For Win32 only. SMTP = localhost ; For Win32 only. sendmail_from = [EMAIL PROTECTED] but I'm danged if I know what to do with that. Any help would be appreciated. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps
is there an easy way to search through unixtimestamps ? like Select * from SessionTable Where ConvertToMonthFunction(unixTimeStamp) = 'March' ? be great to use the Variable function within phpmyAdmin many thanks g -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps
SELECT date_format(FROM_UNIXTIME(875996580),'%Y-%m-%d'); is what I used yesterday to get around this problem bastien From: Graham Anderson [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps Date: Tue, 22 Mar 2005 12:10:15 -0800 is there an easy way to search through unixtimestamps ? like Select * from SessionTable Where ConvertToMonthFunction(unixTimeStamp) = 'March' ? be great to use the Variable function within phpmyAdmin many thanks g -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps
Graham Anderson wrote: is there an easy way to search through unixtimestamps ? like Select * from SessionTable Where ConvertToMonthFunction(unixTimeStamp) = 'March' ? be great to use the Variable function within phpmyAdmin many thanks g On Mysql (works on 4.0 atleast), you can use the FROM_UNIXTIME() function to convert a unix timestamp back to universal date/time format. From then on you can use any standard mysql date/time functions to compare the month. So your query becomes something like: SELECT * FROM SessionTable WHERE MONTH(from_unixtime(527115)) = 3; MONTH() returns a numeric month number from 1-12. See the mysql documentation on date and time functions here: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Hope this helps. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps
So how would you build the query without knowing the unix TimeStamp as '527115' is the current date ? I am trying to build the query so it dynamically know what to look for like SELECT * FROM SessionTable WHERE MONTH(from_unixtime(all available records)) = 'March'; is something like this possible ? many thanks g On Mysql (works on 4.0 atleast), you can use the FROM_UNIXTIME() function to convert a unix timestamp back to universal date/time format. From then on you can use any standard mysql date/time functions to compare the month. So your query becomes something like: SELECT * FROM SessionTable WHERE MONTH(from_unixtime(527115)) = 3; MONTH() returns a numeric month number from 1-12. See the mysql documentation on date and time functions here: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Hope this helps. On Mar 22, 2005, at 1:35 PM, Bastien Koert wrote: SELECT date_format(FROM_UNIXTIME(875996580),'%Y-%m-%d'); is what I used yesterday to get around this problem bastien From: Graham Anderson [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps Date: Tue, 22 Mar 2005 12:10:15 -0800 is there an easy way to search through unixtimestamps ? like Select * from SessionTable Where ConvertToMonthFunction(unixTimeStamp) = 'March' ? be great to use the Variable function within phpmyAdmin many thanks g -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps
SELECT * FROM SessionTable WHERE date_format((from_unixtime(fieldname),'%M') = 'March'; should do the trick bastien From: Graham Anderson [EMAIL PROTECTED] To: php-db@lists.php.net Subject: Re: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps Date: Tue, 22 Mar 2005 15:06:35 -0800 So how would you build the query without knowing the unix TimeStamp as '527115' is the current date ? I am trying to build the query so it dynamically know what to look for like SELECT * FROM SessionTable WHERE MONTH(from_unixtime(all available records)) = 'March'; is something like this possible ? many thanks g On Mysql (works on 4.0 atleast), you can use the FROM_UNIXTIME() function to convert a unix timestamp back to universal date/time format. From then on you can use any standard mysql date/time functions to compare the month. So your query becomes something like: SELECT * FROM SessionTable WHERE MONTH(from_unixtime(527115)) = 3; MONTH() returns a numeric month number from 1-12. See the mysql documentation on date and time functions here: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html Hope this helps. On Mar 22, 2005, at 1:35 PM, Bastien Koert wrote: SELECT date_format(FROM_UNIXTIME(875996580),'%Y-%m-%d'); is what I used yesterday to get around this problem bastien From: Graham Anderson [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Newbie: phpmyadmin and searching Unix Timestamps Date: Tue, 22 Mar 2005 12:10:15 -0800 is there an easy way to search through unixtimestamps ? like Select * from SessionTable Where ConvertToMonthFunction(unixTimeStamp) = 'March' ? be great to use the Variable function within phpmyAdmin many thanks g -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie Setup Trouble
Hi Mike I am using the same book and am very much a newbie also, I had loads of problems just like your having. The best way I think just to set it up and run programs is to use Easyphp. You can get it at www.easyphp.org. It will install everything for you (php, mysql + apache) in one package. Hope this helps Vikas [EMAIL PROTECTED] wrote: php-db Digest 7 Feb 2005 14:55:25 - Issue 2811 Topics (messages 38485 through 38486): Newbie Setup Trouble 38485 by: Mike Rondeau mysqli connection problem 38486 by: Denis Gerasimov Administrivia: To subscribe to the digest, e-mail: [EMAIL PROTECTED] To unsubscribe from the digest, e-mail: [EMAIL PROTECTED] To post to the list, e-mail: php-db@lists.php.net -- ATTACHMENT part 2 message/rfc822 To: php-db@lists.php.net From: Mike Rondeau Date: Sun, 6 Feb 2005 17:57:17 -0800 Subject: Newbie Setup Trouble Hello, I have just begun study of using PHP, MySQL and Apache server. Right now I'm following a book, PHP and MySQL For Dummies and am having trouble already. I must be a REAL dummy! I have installed all 3 programs but something is not right somewhere. Being so new to this I am having a real time trying to figure out which program is messed up. Certain things work, but others don't. For instance, my book told me how to make a phpinfo.php page, which works fine. But it also tells me to create some other pages to test if PHP and MySQL are working or not. I followed the scripts provided exactly but keep getting syntax errors, even though the script is just what the authors wrote. For instance, it said to make a page and call it mysql_up.php. Here's the script for it: $host=localhost; $user=blablablabla; $password=blablabla; mysql_connect($host,$user,$password); $sql=show status; $result = mysql_query($sql); if ($result == 0) { echo Error . mysql_errno() . : . mysql_error() . ; } else { ? Variable_nameValue for ($i = 0; $i mysql_num_rows($result); $i++) { echo ; $row_array = mysql_fetch_row($result); for ($j = 0; $j mysql_num_fields($result); $j++) { echo . $row_array[$j] . ; } echo ; } ? But when I open it in my browser I get this error: Fatal error: Call to undefined function mysql_connect() in C:\Program Files\Apache Group\Apache2\htdocs\mysql_up.php on line 10 Could anyone of you smart people point me in the right direction? I'd be pulling my hair out if I had any left :) I can't make any progress with this study until I know my 3 programs are talking to eachother properly, but my book gives no info on this error. Thanks a millionfold for any advice, and I appologise for the length of this post. Sincerely, Mike Rondeau ATTACHMENT part 3 message/rfc822 From: Denis Gerasimov To: , MySQL General List , PHP General List Date: Mon, 7 Feb 2005 17:52:50 +0300 Subject: mysqli connection problem Hello, This question was asked many times, but I can't find a good answer. I am getting this error message while trying to connect to MySQL server (PHP + PEAR::DB_DataObject): Can't connect to local MySQL server through socket '' (111) (notice '' - is that right?) Does anyone have any ideas about how to get rid of this error? Configuration: * RedHat Enterprise Linux AS3 * Apache 2.0.46 * PHP 5.0.2 * MySQL 4.1.9, mysqli ext. Thanks! - ALL-NEW Yahoo! Messenger - all new features - even more fun!
[PHP-DB] Newbie needs help with multiple MySQL databases
Hi, im fairly new to php and mysql and need a little help. im running a forum community and would like to have it run on multiple databases because of performance issues others have encountered using the same software on on database. My question is is it possible to have the software connect to 2 different databases depending on which is needed? and if so will users need to login again when accessing the second database (their user info will only be on the 1st database.) Thank You -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] [Newbie] switch, if, or something to choose queries and echo formatting--PLEASE help
So I attempted to do this: switch($_GET[page]){ $count=1; $table = mysql_query(SELECT * FROM thumbs,$db); echo(table border='0' cellspacing='1' cellpadding='1' width='300' align='center'tr); while ( $r = mysql_fetch_array($table) ) : if ($count==4){ echo (/trtrtd span='4'img src='images/spacer.gif' height='10' width='1'/td/trtr); $count=1; }else{ $id=$r[id]; $pic=$r[pic]; $thumb=$r[thumb]; $pname=$r[pname]; $pcom=$r[pcom]; echo(td align='left' valign='top' width='100'a href='$pic' target='blank'img src='$thumb' border='0' align='left'/abr clear='all'b$pname/bbri$pcom/i/td); $count++; } endwhile; break; Which did not work. I wondered if that was too much for the case or if you couldn't nest. PHP.net says The switch statement executes line by line (actually, statement by statement). In the beginning, no code is executed. Only when a case statement is found with a value that matches the value of the switch expression does PHP begin to execute the statements. PHP continues to execute the statements until the end of the switch block, or the first time it sees a break statement. Which made me think otherwise--but, hey, something's wrong. Yip, the logic of the switch statement. You simply don't have a case statement. I must confess I don't understand fully what you are trying to do yet but I don't think this is the way to go. The above extract should be enough to see what I mean... Only when a case statement is found with a value that matches the value of the switch expression does PHP begin to execute the statements. You don't have one so... it won't ever start! Cheers Antoine -- G System, The Evolving GUniverse - http://www.g-system.at -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie Question - mysql_select_db fails with access denied.
Hi Folks, I want to do something like the following: $db = mysql_connect(localhost, myuser) or die(Connect failed : . mysql_error()); mysql_select_db(mydb,$db) or die(SELECT_DB failed : . mysql_error ()); $result = mysql_query(SELECT * FROM mytable, $db) or die(Query failed : . mysql_error()); The script does not proceed beyond the mysql_select_db line. I get this error: SELECT_DB failed: Access denied for user ''@'localhost' to database 'mydb' What happened to the user information. I have granted all neccessary rights to myuser. But it seems that its even not caring about the user. I have MySQL 4.1.4 and PHP 4.3.8-2.1 on a Fedore Core 2 System running. Thanks Regards, Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie Question - mysql_select_db fails with access denied.
This might be a stupid idea, but did you remember to issue the Flush Privelages; command to the mysql database after granting your user all the rights? --John Christian Schlaefcke [EMAIL PROTECTED] wrote: Hi Folks, I want to do something like the following: $db = mysql_connect(localhost, myuser) or die(Connect failed : . mysql_error()); mysql_select_db(mydb,$db) or die(SELECT_DB failed : . mysql_error ()); $result = mysql_query(SELECT * FROM mytable, $db) or die(Query failed : . mysql_error()); The script does not proceed beyond the mysql_select_db line. I get this error: SELECT_DB failed: Access denied for user ''@'localhost' to database 'mydb' What happened to the user information. I have granted all neccessary rights to myuser. But it seems that it´s even not caring about the user. I have MySQL 4.1.4 and PHP 4.3.8-2.1 on a Fedore Core 2 System running. Thanks Regards, Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php - Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers!
RE: [PHP-DB] Newbie Question - mysql_select_db fails with access denied.
Hi try $db = mysql_connect(localhost, myuser,mypassword) - without it you are not submitting a password Peter -Original Message- From: Christian Schlaefcke [mailto:[EMAIL PROTECTED] Sent: 24 September 2004 13:31 To: [EMAIL PROTECTED] Subject: [PHP-DB] Newbie Question - mysql_select_db fails with access denied. Hi Folks, I want to do something like the following: $db = mysql_connect(localhost, myuser) or die(Connect failed : . mysql_error()); mysql_select_db(mydb,$db) or die(SELECT_DB failed : . mysql_error ()); $result = mysql_query(SELECT * FROM mytable, $db) or die(Query failed : . mysql_error()); The script does not proceed beyond the mysql_select_db line. I get this error: SELECT_DB failed: Access denied for user ''@'localhost' to database 'mydb' What happened to the user information. I have granted all neccessary rights to myuser. But it seems that its even not caring about the user. I have MySQL 4.1.4 and PHP 4.3.8-2.1 on a Fedore Core 2 System running. Thanks Regards, Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Newbie Question - mysql_select_db fails with access denied.
I have chosen an empty password. I know that this is a security problem. Im using it for testing at the moment only. I use it because there also seems to be an issue with php connecting to mysql servers with verions 4.1. When trying to connect with password I get this: Connect failed : Client does not support authentication protocol requested by server; consider upgrading MySQL client. Because I want to track down issue after issue and not anything at once I decided to find out why mysql_select_db fails first. What really makes me wonder in my tiny example is that the error message does not say something like: SELECT_DB failed: Access denied for user 'myuser'@'localhost' to database 'mydb' instead it says: SELECT_DB failed: Access denied for user ''@'localhost' to database 'mydb' So I think that the username is not interpreted at all. Why not? Regards, Chris Am Fr, den 24.09.2004 um 13:43 Uhr +0100 schrieb Peter Lovatt: Hi try $db = mysql_connect(localhost, myuser,mypassword) - without it you are not submitting a password Peter -Original Message- From: Christian Schlaefcke [mailto:[EMAIL PROTECTED] Sent: 24 September 2004 13:31 To: [EMAIL PROTECTED] Subject: [PHP-DB] Newbie Question - mysql_select_db fails with access denied. Hi Folks, I want to do something like the following: $db = mysql_connect(localhost, myuser) or die(Connect failed : . mysql_error()); mysql_select_db(mydb,$db) or die(SELECT_DB failed : . mysql_error ()); $result = mysql_query(SELECT * FROM mytable, $db) or die(Query failed : . mysql_error()); The script does not proceed beyond the mysql_select_db line. I get this error: SELECT_DB failed: Access denied for user ''@'localhost' to database 'mydb' What happened to the user information. I have granted all neccessary rights to myuser. But it seems that its even not caring about the user. I have MySQL 4.1.4 and PHP 4.3.8-2.1 on a Fedore Core 2 System running. Thanks Regards, Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie Question - mysql_select_db fails with access denied.
You're right: if you use mysql_connect(localhost, root,); you''ll get acces denied for user [EMAIL PROTECTED] if you use mysql_connect(localhost); you'll get Access denied for user @localhost I'll google for a while... but.. to solve YOUR problem, just use: mysql_connect(localhost,user,); Good luck! On Fri, 24 Sep 2004 14:56:35 +0200, Christian Schlaefcke [EMAIL PROTECTED] wrote: I have chosen an empty password. I know that this is a security problem. I´m using it for testing at the moment only. I use it because there also seems to be an issue with php connecting to mysql servers with verions 4.1. When trying to connect with password I get this: Connect failed : Client does not support authentication protocol requested by server; consider upgrading MySQL client. Because I want to track down issue after issue and not anything at once I decided to find out why mysql_select_db fails first. What really makes me wonder in my tiny example is that the error message does not say something like: SELECT_DB failed: Access denied for user 'myuser'@'localhost' to database 'mydb' instead it says: SELECT_DB failed: Access denied for user ''@'localhost' to database 'mydb' So I think that the username is not interpreted at all. Why not? Regards, Chris Am Fr, den 24.09.2004 um 13:43 Uhr +0100 schrieb Peter Lovatt: Hi try $db = mysql_connect(localhost, myuser,mypassword) - without it you are not submitting a password Peter -Original Message- From: Christian Schlaefcke [mailto:[EMAIL PROTECTED] Sent: 24 September 2004 13:31 To: [EMAIL PROTECTED] Subject: [PHP-DB] Newbie Question - mysql_select_db fails with access denied. Hi Folks, I want to do something like the following: $db = mysql_connect(localhost, myuser) or die(Connect failed : . mysql_error()); mysql_select_db(mydb,$db) or die(SELECT_DB failed : . mysql_error ()); $result = mysql_query(SELECT * FROM mytable, $db) or die(Query failed : . mysql_error()); The script does not proceed beyond the mysql_select_db line. I get this error: SELECT_DB failed: Access denied for user ''@'localhost' to database 'mydb' What happened to the user information. I have granted all neccessary rights to myuser. But it seems that it´s even not caring about the user. I have MySQL 4.1.4 and PHP 4.3.8-2.1 on a Fedore Core 2 System running. Thanks Regards, Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Pablo M. Rivas. http://pmrivas.ipupdater.com http://www.r3soft.com.ar --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie Question - mysql_select_db fails with access denied.
When I switch back to user root with empty password and use mysql_connect(localhost, root) it works. I just found out when I set the permission for myuser to localhost instead of any it works also for myuser. Now I have to check out why I cant use passwords. Maybe the PHP Version Im running is not ready to work with MySQL 4.1.4? Any Ideas? Regards, Chris Am Fr, den 24.09.2004 um 12:04 Uhr -0300 schrieb Pablo M. Rivas: You're right: if you use mysql_connect(localhost, root,); you''ll get acces denied for user [EMAIL PROTECTED] if you use mysql_connect(localhost); you'll get Access denied for user @localhost I'll google for a while... but.. to solve YOUR problem, just use: mysql_connect(localhost,user,); Good luck! On Fri, 24 Sep 2004 14:56:35 +0200, Christian Schlaefcke [EMAIL PROTECTED] wrote: I have chosen an empty password. I know that this is a security problem. Im using it for testing at the moment only. I use it because there also seems to be an issue with php connecting to mysql servers with verions 4.1. When trying to connect with password I get this: Connect failed : Client does not support authentication protocol requested by server; consider upgrading MySQL client. Because I want to track down issue after issue and not anything at once I decided to find out why mysql_select_db fails first. What really makes me wonder in my tiny example is that the error message does not say something like: SELECT_DB failed: Access denied for user 'myuser'@'localhost' to database 'mydb' instead it says: SELECT_DB failed: Access denied for user ''@'localhost' to database 'mydb' So I think that the username is not interpreted at all. Why not? Regards, Chris Am Fr, den 24.09.2004 um 13:43 Uhr +0100 schrieb Peter Lovatt: Hi try $db = mysql_connect(localhost, myuser,mypassword) - without it you are not submitting a password Peter -Original Message- From: Christian Schlaefcke [mailto:[EMAIL PROTECTED] Sent: 24 September 2004 13:31 To: [EMAIL PROTECTED] Subject: [PHP-DB] Newbie Question - mysql_select_db fails with access denied. Hi Folks, I want to do something like the following: $db = mysql_connect(localhost, myuser) or die(Connect failed : . mysql_error()); mysql_select_db(mydb,$db) or die(SELECT_DB failed : . mysql_error ()); $result = mysql_query(SELECT * FROM mytable, $db) or die(Query failed : . mysql_error()); The script does not proceed beyond the mysql_select_db line. I get this error: SELECT_DB failed: Access denied for user ''@'localhost' to database 'mydb' What happened to the user information. I have granted all neccessary rights to myuser. But it seems that its even not caring about the user. I have MySQL 4.1.4 and PHP 4.3.8-2.1 on a Fedore Core 2 System running. Thanks Regards, Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Pablo M. Rivas. http://pmrivas.ipupdater.com http://www.r3soft.com.ar --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] [Newbie] switch, if, or something to choose queries and echo formatting--PLEASE help
[Newbie] switch, if, or something to choose queries and echo formatting--PLEASE help Please bear with me, I will be telling you step by step what I have tried, and what I want. I'm a newbie to this whole thing. I've had no trouble getting php and mysql running on computer and getting a test database going so I could perform some sample queries. I have a personal website with a fair amount of content--and more waiting in the wings for whenever I can figure this php-mysql stuff out. Certain pages will contain 'image galleries' that will show thumbnails and link to larger images, possibly with a name or a comment underneath the picture. I have code for that, which works fine--by itself. Other pages will contain lists of titles that will link to stories or essays. That will be easy to do, pretty much the same as the image gallery, but without the thumbnail or the need for columns. What I am NOT finding easy to do is working out how I can get php to recognize which page is attempting to be viewed and then query the appropriate table and echo the results in the appropriate format. I WILL be manually typing in my links, because the layout I want to use involves a drop-down menu by ALA that I've altered a bit with images ( http://www.memoira.com/testing/3col/ - sample of layout). The format will be subsections.php?section=something?page=something OR subsections.php?page=something The first thing I did was attempt to import variables and use a series of if statements, ie: if ($page==animation) {include design.php;} Which didn't work at all. Then someone suggested to me using this instead: switch($_GET[section]){ case design: include(design.php); break; Which DID work--but I can hardly use that for every page--otherwise I'll end up making as many php files as I did html files before, which is totally not the point of this, is it? So I attempted to do this: switch($_GET[page]){ $count=1; $table = mysql_query(SELECT * FROM thumbs,$db); echo(table border='0' cellspacing='1' cellpadding='1' width='300' align='center'tr); while ( $r = mysql_fetch_array($table) ) : if ($count==4){ echo (/trtrtd span='4'img src='images/spacer.gif' height='10' width='1'/td/trtr); $count=1; }else{ $id=$r[id]; $pic=$r[pic]; $thumb=$r[thumb]; $pname=$r[pname]; $pcom=$r[pcom]; echo(td align='left' valign='top' width='100'a href='$pic' target='blank'img src='$thumb' border='0' align='left'/abr clear='all'b$pname/bbri$pcom/i/td); $count++; } endwhile; break; Which did not work. I wondered if that was too much for the case or if you couldn't nest. PHP.net says The switch statement executes line by line (actually, statement by statement). In the beginning, no code is executed. Only when a case statement is found with a value that matches the value of the switch expression does PHP begin to execute the statements. PHP continues to execute the statements until the end of the switch block, or the first time it sees a break statement. Which made me think otherwise--but, hey, something's wrong. So I've reviewed and changed and altered and edited and queried friends that use ASP and now I'm just thoroughly frustrated. It seems to me that putting in all the echo stuff is screwing things up, so I figured I'd have to come up with some other kind of method... So I thought I WOULD go with both a section designation and a page designation. The section designation would include design.php which would contain the code for the multi-column image gallery (all sections/tables containing that sort of information can be named and formatted alike), but the table in the query would be a variable. So, subsections.php would contain the connection to the mysql server and database, and a series of cases like: switch($_GET[section]){ case design: include(design.php); break; Then design.php would contain a series of cases like switch($_GET[page]){ case animation: $page=thumbs; break; as well as $count=1; $table = mysql_query(SELECT * FROM $page); echo(table border='0' cellspacing='1' cellpadding='1' width='300' align='center'tr); while ( $r = mysql_fetch_array($table) ) : if ($count==4){ echo (/trtrtd span='4'img src='images/spacer.gif' height='10' width='1'/td/trtr); $count=1; }else{ $id=$r[id]; $pic=$r[pic]; $thumb=$r[thumb]; $pname=$r[pname]; $pcom=$r[pcom]; echo(td align='left' valign='top' width='100'a href='$pic' target='blank'img src='$thumb' border='0' align='left'/abr clear='all'b$pname/bbri$pcom/i/td); $count++; } endwhile; echo (/tr/table); However, this is not working either. The link says: subsections.php?section=design?page=animation It's giving me my default case Error message I've set. I'm baffled--how do MOST people accomplish this? Is my insistance on the manual linking throwing everything off? Is my insistence on keeping the site organized almost exactly as it was organized before making it impossible? Am I just stupid? I've
[PHP-DB] Newbie Questions
I'm just getting started with PHP and MySQL (how do you say that, my ess kew wll, my sequel, ???). I have a background with C programming and many years ago, I played with dBASE. I want to set up a directory with the following fields: surname firstname spousename housenumber street phone email (contents is either an email address or a minus sign) I'd like to present a webpage to give the view these choices: 1) viewing the entire directory sorted by surname 2) viewing the entire directory sorted by street and then by housenumber, i.e., by address 3) viewing all of the entire records of all who meet a simple criterion: piece of an email address (eg, comcast would list everyone that had comcast in the eail field), piece of and of the names or streets, etc. I have PHP and MySQL for Dummies. The only thing I haven't seen that (I think) I need for the above is how to sort on two fields. Can anyone help me get started? It seems like a pretty easy things to do. Perhaps if someone has already done this, they might share their stuff with me. I'm much better at hacking away at other people's stuff than developing my own from scratch! :-) Many thanks, Pete Holsberg Columbus, NJ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] retry, this time with code [was: [PHP-DB] [newbie] Form to email *and* insert row to MySQL]
My last email shows on the archive but it also bounced back to me so I am reposting it. Sorry of this is a double-post. Also, I am adding this time the source code down below which I should have probably done last time around. Any help is much appreciated, Ben. repost Hello guys, I have an HTML form with the action attribute set to the famous FormMail.pl and it works beautifully. However, I also want to insert the data into a MySQL database. Initially, I thought that I would let FormMail validate the data, send the email, and then redirect to a PHP script that would use the $_POST array to INSERT it into my db [see source below]. Of course, it didn't work. AFAICT, both the FormMail script and the PHP script need to be called from the form's action attribute. 1 - Is there a way to call them both? From lurking around and reading tutorials, I understand that it is possible to send emails with PHP and that I don't need to use FormMail at all. However, I have been told that FormMail is a relatively safe script that won't let hackers exploit either the server or myself. I am not quite sure what such exploits might be, but I trust that the hackers are... 2 - If I am to drop FormMail, what PHP script should I use to protect my, and the server's, security? Which brings us to the next point: the PHP script that I currently use is very straightforward ([see below]) and the subuser has only INSERT privileges. 3 - Am I putting anything (db, server) in a danger with this script? Cheers, Ben /repost source [HTML Form] form method=post action=http://site.com/cgi-bin/FormMail.pl; / input type=hidden name=redirect value=http://site.com/script.php; / input type=hidden name=required value=realname / pName: input type=text name=realname size=35 //p [...] [script.php] $name = $_POST['realname']; mysql_connect(localhost, subuser, password)or die(ERROR: .mysql_error()); mysql_select_db(my_db)or die(ERROR: .mysql_error()); mysql_query(INSERT INTO `my_table` (`id`, `name`) VALUES ('','$realname')); header('Location: http://site.com/thankyou.html'); /source -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] [newbie] Form to email *and* insert row to MySQL
Hello guys, I have an HTML form with the action attribute set to the famous FormMail.pl and it works beautifully. However, I also want to insert the data into a MySQL database. Initially, I thought that I would let FormMail validate the data, send the email, and then redirect to a PHP script that would use the $_POST array to INSERT it into my db. Of course, it didn't work. AFAICT, both the FormMail script and the PHP script need to be called from the form's action attribute. 1 - Is there a way to call them both? From lurking around and reading tutorials, I understand that it is possible to send emails with PHP and that I don't need to use FormMail at all. However, I have been told that FormMail is a relatively safe script that won't let hackers exploit either the server or myself. I am not quite sure what such exploits might be, but I trust that the hackers are... 2 - If I am to drop FormMail, what PHP script should I use to protect my, and the server's, security? Which brings us to the next point: the PHP script that I currently use is very straightforward (connect, select_db, INSERT) and the subuser has only INSERT privileges. 3 - Am I putting anything (db, server) in a danger with this script? Cheers, Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie inserting into a database
Here is what I have so far? ? $DBname = testdatabase; $host=localhost; $user=root; $password = password; $dbconnect = @mysql_connect('$host','$user','$password','$DBname'); I know this is wrong since I am not connecting. What I want to do is INSERT several values into a database. What am I doing wrong and what do I need to do after I connect (The Code)? 7 day old problem here and pulling at my hair! Appreciate it! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie inserting into a database
Remove the single quotes or change to double quotes around the variables. Here is what I have so far? ? $DBname = testdatabase; $host=localhost; $user=root; $password = password; $dbconnect = @mysql_connect('$host','$user','$password','$DBname'); I know this is wrong since I am not connecting. What I want to do is INSERT several values into a database. What am I doing wrong and what do I need to do after I connect (The Code)? 7 day old problem here and pulling at my hair! Appreciate it! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie inserting into a database
Remove the single quotes or change to double quotes around the variables. (in the mysql_connect() statement) Here is what I have so far? ? $DBname = testdatabase; $host=localhost; $user=root; $password = password; $dbconnect = @mysql_connect('$host','$user','$password','$DBname'); I know this is wrong since I am not connecting. What I want to do is INSERT several values into a database. What am I doing wrong and what do I need to do after I connect (The Code)? 7 day old problem here and pulling at my hair! Appreciate it! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie question
Hi! I'm new to PHP, so this may be an easy question. I tried to make a simple form-page, where the user enters his name, and on the following page it says Hello $name. What happens is that on the second page it says soemthing like unknown identifier $name. I'm using Apache 2 so that may be causing the problem, cause this is a clear textbook example: -- first.php -- html head titleFirst/title /head body bgcolor=#ff text=#00 link=#cbda74 vlink=#808040 alink=#808040 form action=second.php method=post bWhats your name?/b input type=text name=name size=20 maxlength=20 value=br input type=submit value=go! /form /body --- second.php html head titleSecond/title /head body bgcolor=#ff text=#00 link=#cbda74 vlink=#808040 alink=#808040 ? print Hello $name!; ? /body /html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Newbie question
Try: ? print Hello .$_POST[name]; ? -Original Message- From: Gajo Csaba [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 24, 2004 4:10 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Newbie question Hi! I'm new to PHP, so this may be an easy question. I tried to make a simple form-page, where the user enters his name, and on the following page it says Hello $name. What happens is that on the second page it says soemthing like unknown identifier $name. I'm using Apache 2 so that may be causing the problem, cause this is a clear textbook example: -- first.php -- html head titleFirst/title /head body bgcolor=#ff text=#00 link=#cbda74 vlink=#808040 alink=#808040 form action=second.php method=post bWhats your name?/b input type=text name=name size=20 maxlength=20 value=br input type=submit value=go! /form /body --- second.php html head titleSecond/title /head body bgcolor=#ff text=#00 link=#cbda74 vlink=#808040 alink=#808040 ? print Hello $name!; ? /body /html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] newbie question
hi, i am relatively new to php and new to this email list. i have what i think are fairly simple questions about using mysql and php. i have done some research and can't seem to find the answer i need. # 1. i want to set the date format to display dates in a format other than the standard mysql -mm-dd format. i have tried using the mysql DATE_FORMAT but i can's seem to get it to work... ideally i'd like to display dates as 2 digit date followed by three letter month abbreviation and leave the year off completely... example: 13 feb # 2. i want to hide entries that are newer than the current date AND hide entries older than 365 days. i apologize if this is the wrong place to ask these questions. if someone could point me in the right direction i would appreciate it. here is the url and code (minus the login info) http://www.broadcastatic.com ?php $db = mysql_connect(localhost, [loginnamehere], [passwdhere]); mysql_select_db([databasenamehere],$db); // display individual record if ($date) { $result = mysql_query(SELECT * FROM [tablenamehere] WHERE date= '$date' ,$db); $myrow = mysql_fetch_array($result); // do not use now printf(img src=\../images/icons/%s.gif\br\n, $myrow[date]); printf(hosted by %sbr\n, $myrow[dj]); printf(broadcast on %sbr\n, $myrow[date]); printf(%s\nbrbrbr, $myrow[location]); printf(-- %s\nbrbr, $myrow[entry]); printf(a href=\archive/mp3/$date.mp3\mp3 link/a\nbrbr, $myrow[date]); include(archive/index/$date.php); } else { // display list of shows by date $result = mysql_query(SELECT * FROM [tablenamehere] WHERE 1 ORDER BY 'date' DESC,$db); if ($myrow = mysql_fetch_array($result)) { // display list if there are records to display do { printf(div id=\col1\a href=\%s?date=%s\%s/a /div div id=\col2\img src=\archive/images/icon/%s.gif\ /div div id=\col3\b%s/bbr%s/divbr\n, $PHP_SELF, $myrow[date], $myrow[date], $myrow[date], $myrow[dj], $myrow[entry]); } while ($myrow = mysql_fetch_array($result)); } else { // no records to display echo Sorry, no records were found!; } } ? THANKS! tommy birchett http://www.broadcastatic.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] newbie question
On Fri, 13 Feb 2004, t wrote: hi, i am relatively new to php and new to this email list. i have what i think are fairly simple questions about using mysql and php. i have done some research and can't seem to find the answer i need. # 1. i want to set the date format to display dates in a format other than the standard mysql -mm-dd format. i have tried using the mysql DATE_FORMAT but i can's seem to get it to work... ideally i'd like to display dates as 2 digit date followed by three letter month abbreviation and leave the year off completely... example: 13 feb Use date() Documentation: http://php.net/date # 2. i want to hide entries that are newer than the current date AND hide entries older than 365 days. Limit your SQL to ... where datenow() and datedate_sub(now(), interval 365 day) ... if date is in datetime format If in unixtime format, convert to unixtime for those functions ... where dateunix_timestamp() and date(unix_timestamp()-(365*86400)) ... Documentation: http://mysql.com/date_sub (should redirect to http://www.mysql.com/doc/en/Date_and_time_functions.html) --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] newbie question on data accumulation
Hi all: Curious as to what happens after data is repeatedly selected from a mysql table overtime. Does it accumulate as junk data, stored at some location and eventually slow down the database/program/server? Does any purging have to take place? Thanks J __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie help
I need some help to install and configure php I have tried downloading it a dozen times to configure it on Apache on xp but I cannot seem to get it to talk to my sql could someone provide me with an idiots guide to installing it please? Thanks in advance Nicci
RE: [PHP-DB] Newbie help
http://www.php.net/manual/en/install.windows.php -Original Message- From: Nicola Hartland [mailto:[EMAIL PROTECTED] Sent: Saturday, September 06, 2003 4:29 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Newbie help I need some help to install and configure php I have tried downloading it a dozen times to configure it on Apache on xp but I cannot seem to get it to talk to my sql could someone provide me with an idiots guide to installing it please? Thanks in advance Nicci -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] newbie lost on data import...
I have a 1400 line text file. TAB delimited. 1974-01-13 8 Cold Storage 3 fields. nothing large or complicated. I massaged the data to be in this format thinking it would make the import easier. I can open the file and drop it into an array just fine. I can EXPLODE each array item into it's base parts (date, num, name) just fine. list($strDate,$strNum,$strName) = explode (\t, $lines[$x]); But now I run into problems. The 'date' will not insert into the database. The 'num' will insert into the database. The 'name' will not insert into the database. well, one out of three's not so bad. :/ This is what I'm trying to do... $aryFieldsValues = array( 'eps_id' = $x, 'eps_name' = $strName, 'eps_num' = $strNum, 'eps_air_date' = $strDate); $strTableName = 'cbs_eps'; $objResult = $db-autoExecute($strTableName, $aryFieldsValues, DB_AUTOQUERY_INSERT); I even did this... $strSql = 'INSERT INTO `cbs_eps` ( `eps_id` , `eps_name` , `eps_num` , `eps_air_date` ) '; $strSql .= VALUES ( ; $strSql .= ' . $x . ', ; $strSql .= ' . $strName . ', ; $strSql .= ' . $strNum . ', ; $strSql .= ' . $strDate . ' );; $objResult = $db-query($strSql); Same results. Can anyone tell me what I am doing wrong? Oh, I did add this... // Yank off the RETURN at the end $strName = rtrim($strName); // ESCAPE all the funny stuff! $strName = mysql_real_escape_string($strName); It gives me... \0L\0o\0s\0t\0 \0D\0o\0g = was 'Lost Dog' But it still will not insert into database. :( BTW: you might need to know this... Field Type Attributes Null Default --- eps_idtinyint(4)No 0 cat_idtinyint(4)Yes NULL eps_name varchar(100) Yes NULL eps_num int(11) Yes NULL eps_air_date date Yes -00-00 AND, please, someone, tell me how to make this date string '1974-01-13' into something that mySQL will accept. Thanks for your help. Walter -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie file reading into MySQL
On Monday 07 July 2003 07:44, Kim Kohen wrote: Just one question: why are you storing text files containing DATA into a DB? Why not just store the data in the DB and do away with the text files? Thanks for the reply. I can see now my original wording was a bit unclear. I am, in fact, storing the data only - the text file is just the 'conduit' to get data from InDesign into PHP. InDesign will generate the text file - PHP reads in the data from the file (and than deletes the text file) - MySQL stores it - PHP will report on it. It's just a matter of how to format the file from InDesign. The two options would be line by line eg: Kim Page 8 Sunday, 6 July 2003 8:08:46 PM Ad number 1234-t32 Ad placed on page I'm figuring this way I'd have to use fgets() and go line by line and do 5 separate INSERTs or I could have it as some kind of list eg: Kim, Page 8, Sunday, 6 July 2003 8:08:46 PM, Ad number 1234-t32, Ad placed on page If I do this and get the list into an array, can I have an INSERT statement along the lines of INSERT INTO test (column1, column2, column3, column4, column5) VALUES ($myarray) or do I have to parse the array into individual values before INSERTing? You can't pass the array directly like that. Logically I'd see one INSERT statement being better than five - I'm just not sure how to handle the INSERT from an array. You have to address each element of the array explicitly: INSERT INTO test (column1, column2, column3, column4, column5) VALUES ($myarray[0], $myarray[1], ...) -or- (depending on how you constructed your array: INSERT INTO test (column1, column2, column3, column4, column5) VALUES ($myarray['column1'], $myarray['column2'], ...) You may want to investigate whether formatting the file into something that can be read by fgetcsv() or parse_ini_file() would make things easier. -- 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-db -- /* If people drank ink instead of Schlitz, they'd be better off. -- Edward E. Hippensteel [What brand of ink? Ed.] */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Newbie file reading into MySQL
G'day all This is probably a really basic question but I'm asking advice because I'm a real newbie and want to start off on the right foot. I'm trying to build a PHP/MySQL workflow tracking system in which each movement of an InDesign file generates a text file which contains the tracking data I want to move into the (MySQL) db. It will contain the operators name, a timestamp and a status (incomplete, pending, complete etc) Because I'm setting up the scripts to generate these tracking files, I'm wondering the best format for them to make the PHP reading easy. I've read about the fgets function but I'm not sure whether reading line by line is better than making the file a list of items and then attempting to read the whole thing into an array. Is either one easier considering the eventual placement into the db? I have cobbled together a working script which reads data from text files into a db but have only done it from files with a single word of data and I'm just not sure the best way to format/handle/read multiple lines or items. Cheers and thanks for any advice kim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie file reading into MySQL
On Sunday 06 July 2003 19:04, Kim Kohen wrote: [snip] I'm trying to build a PHP/MySQL workflow tracking system in which each movement of an InDesign file generates a text file which contains the tracking data I want to move into the (MySQL) db. It will contain the operators name, a timestamp and a status (incomplete, pending, complete etc) [snip] Just one question: why are you storing text files containing DATA into a DB? Why not just store the data in the DB and do away with the text files? -- 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-db -- /* If you improve or tinker with something long enough, eventually it will break or malfunction -- Murphy's In Laws n8 */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Newbie file reading into MySQL
G'day Jason Just one question: why are you storing text files containing DATA into a DB? Why not just store the data in the DB and do away with the text files? Thanks for the reply. I can see now my original wording was a bit unclear. I am, in fact, storing the data only - the text file is just the 'conduit' to get data from InDesign into PHP. InDesign will generate the text file - PHP reads in the data from the file (and than deletes the text file) - MySQL stores it - PHP will report on it. It's just a matter of how to format the file from InDesign. The two options would be line by line eg: Kim Page 8 Sunday, 6 July 2003 8:08:46 PM Ad number 1234-t32 Ad placed on page I'm figuring this way I'd have to use fgets() and go line by line and do 5 separate INSERTs or I could have it as some kind of list eg: Kim, Page 8, Sunday, 6 July 2003 8:08:46 PM, Ad number 1234-t32, Ad placed on page If I do this and get the list into an array, can I have an INSERT statement along the lines of INSERT INTO test (column1, column2, column3, column4, column5) VALUES ($myarray) or do I have to parse the array into individual values before INSERTing? Logically I'd see one INSERT statement being better than five - I'm just not sure how to handle the INSERT from an array. Cheers and thanks kim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Newbie Question - PHP and MSACCESS
//Place your request into a variable: $result = odbc_do($connection,select * from events where id=$id); $var = odbc_fetch_row($result); // Now you have an array to work on foreach($var as $line){ //Do your formatting thusly: echo ' trtd' . $line[0] . '?td td' . $line[1] . '/td ... /tr } Hi, Am using php to open a customer's msaccess file. First time for everything. It's a very small database of less then 25 rows, and will always be this small. Am needing to return all the rows to a web page using php at all times. Very simplistic. Am apparently set up correctly on the DSN, and am able to do an odbc_connect: $connection = odbc_connect(meetings,,); Am able to fetch rows, or so it seems: $result = odbc_do($connection,select * from events where id=$id); odbc_fetch_row($result); Am not understanding how to print() the fields in the rows, one after the next, on the same line, and then do the same with the next record, until all records have been presented. Sorry to be lame. Any help would be gratefully received. -Joe
[PHP-DB] Newbie Question - PHP and MSACCESS
Hi, Am using php to open a customer's msaccess file. First time for everything. It's a very small database of less then 25 rows, and will always be this small. Am needing to return all the rows to a web page using php at all times. Very simplistic. Am apparently set up correctly on the DSN, and am able to do an odbc_connect: $connection = odbc_connect(meetings,,); Am able to fetch rows, or so it seems: $result = odbc_do($connection,select * from events where id=$id); odbc_fetch_row($result); Am not understanding how to print() the fields in the rows, one after the next, on the same line, and then do the same with the next record, until all records have been presented. Sorry to be lame. Any help would be gratefully received. -Joe