Re: [PHP-DB] Broken query
How is the last mailing date variable set? Have you tried printing out the queries that are run when it is run manually and when it is run as a cron to find any differences? -patrick On Jul 11, 2010 8:23 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am trying to write a query to select a trivia question, but I don't want the trivia question category to be the same two days in a row so I added a second SELECT syntax to find out what category was used yesterday. This works when I test it live, but doesn't work when it is part of a cron job. How do I get the value of `Bible_trivia_category_reference` from the second SELECT query to be used in the first? What change is needed? Ron SELECT * FROM `verse_of_the_day_Bible_trivia` WHERE `assigned_date` = '-00-00' AND `seasonal_use` = $bible_trivia_application AND `live` =1 AND NOT `Bible_trivia_category_reference` = ( SELECT `Bible_trivia_category_reference` FROM `verse_of_the_day_Bible_trivia` WHERE `assigned_date` = '$last_mailing_date' LIMIT 1 ) ORDER BY RAND() LIMIT 1 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql_close()
According to php.net, if you don't specify a link iedentifier it closes the last nonpersistent connection. http://us3.php.net/mysql_close Patrick -- Sent from my Palm Pre Ron Piggott wrote: Does mysql_close() close all open database connections? How would I specify which connection to close? Ron
Re: [PHP-DB] Storing images
I would say that only if this is going to be a very small project should you think about storing the images in the db. I had a contract job for a real estate company that had stored all their images in the database. They had ~25K rows of data where each image was ~5K and their website was going down weekly. I had to rewrite the entire image upload/retrieval system for their site to change it to folder based storage. Just my thoughts... Thanks patrick On Wed, Feb 3, 2010 at 8:02 AM, Phpster phps...@gmail.com wrote: Sure is. Save the data in a blob field. Lots of examples on the net on how to get it there and back out again. Note that you will also find arguments about whether or not to do it in the first place. I prefer not to, as it causes significant performance issues when the image table gets to a certain size. Bastien Sent from my iPod On Feb 3, 2010, at 2:37 AM, Karl DeSaulniers k...@designdrumm.com wrote: Hello List, Forgive me if this is a noob question, but is it possible to fill a database table with actual image data, IE a jpeg? And then call that data to display the image? Or is it better to just reference it stored on the server somewhere and just put the url in the database? Thanks, 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] Need Help in the below script
It appears that you had a missing bracket or two and you had misspelled 'WHERE' in the query. In your code you were checking if the username and password were correct outside of the while loop. Even though it can be uncommon, if you have multiple users with the same username then you would only be checking the last result, not each row. I changed the query to make it simpler, if you check for the username and password to match in the query, then you only have to check for the returned rows to see if the correct username and password were used. I added a second query to check if the username exists but the password was wrong. For security purposes when a login attempt fails, you should not tell a user whether the username or password was correct, once they know that one of their parameters was correct, it is much easier for them to hack the other parameter You also need to be concerned about SQL injection attacks, you should always escape any data being used in a query. http://us.php.net/manual/en/security.database.sql-injection.php ?php $username = $_POST['username']; $password = $_POST['password']; if($username$password) { $connect= mysql_connect(localhost,root,) or die(couldn't connect); mysql_select_db(phplogin) or die(no db in the list); // escape data to prevent SQL injection attacks $username = mysql_real_escape_string($username); $password = mysql_real_escape_string($password); $query = mysql_query(SELECT * FROM users WHERE username='$username' AND password = '$password';); $numrows = mysql_num_rows($query); if ($numrows == 1) echo you are in; else { $username_result = mysql_query(SELECT * FROM users WHERE username = '$username';); if(mysql_num_rows($username_result) == 0) echo user does not exist; else echo incorrent username and password; } } else die(please enter a username and a password); ? Hope this helps. Thanks, patrick On Fri, Sep 4, 2009 at 5:07 AM, nagendra prasad nagendra802...@gmail.comwrote: Hi all, I am working on my project. I have to create a user regestration page and a login page. I am done with registration page but when I tried to code the login page its not working. Below is the code. Please take a look at script and let me know where am I going wrong. ?php $username=$_POST['username']; $password=$_POST['password']; if($username$password) { $connect= mysql_connect(localhost,root,) or die(couldn't connect); mysql_select_db(phplogin) or die(no db in the list); $query = mysql_query(SELECT * FROM users WHEER username='$username'); $numrows = mysql_num_rows($query); if ($numrows!=0) { echo user dosen't exist; while ($row = mysql_fetch_assoc($query)) { $dbusername = $row['username']; $dbpassword = $row['password']; } if ($username==$dbusername $password==$dbpassword) { echo you are in; } else echo incorrent username and password; else die(user dosent exitst); } else die(please enter a username and a password); } ? -- Guru Prasad Ubuntu Voice GTK+ Forum
Re: [PHP-DB] 500 records (articles) - SELECT ALL or USE WHERE
Just shooting from the hip, I would say the faster method would be to create the 5 different mysql queries. The amount of time taken up by retrieving 500 articles of either blob or text can add up to a significant amount of time, whereas 5 queries that average 10 results per query would result in ~90% decrease in reading data from the database. Performing aggregate mysql functions for most commented and etc are generally very fast and can be done much more quickly than retrieving the whole article, passing to php, storing to memory and then manipulating the data. Thanks patrick 2009/8/4 Martin Zvarík mzva...@gmail.com I need to do about 5 queries: 1) pick the top visited articles 2) pick the recent articles (limit 10) 3) pick the most commented articles etc. Now, I have 2 choices: 1) SELECT ALL records and use PHP for conditions 2) do 5 queries on MySQL --- Imagine it has 500 long text articles. Which of these 2 options is better for performance? Thanks, Martin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Changing part of a query before displaying it
I believe your best bet is to change how you are iterating through the $row data. Since there are only 6 columns of data, I think the simplest solution is to remove the foreach() and handle each column's data independently, ie: while($row = mysql_fetch_row($result10)) { print ' tr td' . $row['first_column'] . '/td td' . $row['second_column'] . '/td td' . $row['third_column']/60 . '/td td' . $row['fourth_column'] . '/td td' . $row['fifth_column'] . '/td td' . $row['sixth_column'] . '/td /tr'; } Thanks patrick On Wed, Jul 15, 2009 at 1:10 PM, Don Collier d...@collierclan.com wrote: I am quite new at this so please bare with me. I am getting a several rows of data from a database to display in a table. One of the fields is in a format that I want to change before putting it in the table. The field is time in seconds, but I want it to be displayed in minutes instead. Below is what I have to get the data into the table. while ($row = mysql_fetch_row($result10)) { print 'tr'; foreach($row as $data) { print td {$data} /td; } print '/tr'; There are 6 columns in the table and the column that I want to change is the 3rd one. How do I go about doing this? Don Collier -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Changing part of a query before displaying it
Yes, the while loop will retrieve all the rows from the database. The foreach loop you had been using was retrieving all the data from each row. The only appreciable difference between your code and mine is in the circumstance, if you added more columns to the database, you would need to add more code to access that data in my code, whereas your code you wouldn't need to add any more code. Thanks patrick On Wed, Jul 15, 2009 at 1:40 PM, Don Collier d...@collierclan.com wrote: Would that work for multiple rows though? This is getting a varying number of rows. Patrick Price wrote: I believe your best bet is to change how you are iterating through the $row data. Since there are only 6 columns of data, I think the simplest solution is to remove the foreach() and handle each column's data independently, ie: while($row = mysql_fetch_row($result10)) { print ' tr td' . $row['first_column'] . '/td td' . $row['second_column'] . '/td td' . $row['third_column']/60 . '/td td' . $row['fourth_column'] . '/td td' . $row['fifth_column'] . '/td td' . $row['sixth_column'] . '/td /tr'; } Thanks patrick On Wed, Jul 15, 2009 at 1:10 PM, Don Collier d...@collierclan.commailto: d...@collierclan.com wrote: I am quite new at this so please bare with me. I am getting a several rows of data from a database to display in a table. One of the fields is in a format that I want to change before putting it in the table. The field is time in seconds, but I want it to be displayed in minutes instead. Below is what I have to get the data into the table. while ($row = mysql_fetch_row($result10)) { print 'tr'; foreach($row as $data) { print td {$data} /td; } print '/tr'; There are 6 columns in the table and the column that I want to change is the 3rd one. How do I go about doing this? Don Collier --PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Cannot print the data from database in the webpage
I believe your problem is that the mysqli_fetch_array function on line 39 returns an enumerated array and the extract function on line 41 takes the values in an array and assigns it to an associative array based on the keys. Since the keys in an enumerated array are numbers, those are the variable names. I believe you will have an easier time if you do the following: while ($row=mysqli_fetch_array($data)) { $building = $row[0]; $parking_lot = $row[1]; $month = $row[2]; $day = $row[3]; $occupancy = $row[4]; $empty_spaces = $row[5]; $special_days = $row[6]; echo tr\n td$building/td\n td$parking_lot/td\n td$month/td\n td$day/td\n td$occupancy/td\n td$empty_spaces/td\n td$special_days/td\n /tr\n; echo trtd colspan='7'hr //td/tr\n; } Now it is important in the order with which the fields are returned from the database since that will affect the row values when you are assigning them to variables (ie- if the day is returned first in the array it should be $day = $row[0] ) Hope that helps Thanks patrick Sent from: Decatur Ga United States. On Sat, Feb 14, 2009 at 12:05 AM, Sashikanth Gurram sashi...@vt.edu wrote: Hi all, I am trying to run a query to retrieve data from mysql database and display it in a webpage in a table. The query is running fine, but the the data is not appearing on the webpage. All I see is a webpage with few empty cells. The echo command starting from line 42 is something like this *echo tr\n td$building/td\n td$parking_lot/td\n td$month/td\n td$day/td\n td$occupancy/td\n td$empty_spaces/td\n td$special_days/td\n /tr\n;* The variables I have used here ie., $building, $parking_lot etc are nothing but the column names in the mysql database ie., building, parking_lot etc. I hope I did not mess up in that sense. But, I do not know the reason for the non appearance of the data. I am posting the whole code below. Also I am attaching a screenshot of the display in the webpage. If anyone has any answer please let me know. Thanks Sashi ?php // Connects to your Database $host=**; $user=**; $password=**; $dbname=**; $cxn=mysqli_connect($host, $user, $password, $dbname) ; if (!$cxn=mysqli_connect($host, $user, $password, $dbname)) { $error=mysqli_error($cxn); echo $error; die(); } else { echo Connection established successfully; } $sql=SELECT * FROM occupancy; $data = mysqli_query($cxn,$sql); if (!$data=mysqli_query($cxn,$sql)) { $error=mysqli_error($cxn); echo $error; die(); } else { echo br; echo Query sent successfully; } echo br; echo h1 OCCUPANCY h1; echo table cellspacing='0'; echo trtd colspan='7'hr //td/tr; while ($row=mysqli_fetch_array($data)) { extract($row); echo tr\n td$building/td\n td$parking_lot/td\n td$month/td\n td$day/td\n td$occupancy/td\n td$empty_spaces/td\n td$special_days/td\n /tr\n; echo trtd colspan='7'hr //td/tr\n; } echo /table\n; ? -- ~ ~ Sashikanth Gurram Graduate Research Assistant Department of Civil and Environmental Engineering Virginia Tech Blacksburg, VA 24060, USA -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] db privileges
Hey all, I ran across a problem while trying to run a delete query. The website that I am working on has multiple users in the db for various logins and I was inadvertently using a less-privileged account and found I couldn't make delete queries run successfully. I eventually found out the problem and changed the db connection but I was wondering if there is a php function and/or mysql function that can be run from php to find out the privileges for a particular db connection. Any ideas? PHP Ver. 5.2.1 MySQL Ver. 5.0.27 on Linux Thanks patrick
Re: [PHP-DB] db privileges
Hey Frank, I wasn't able to run the query: select * from mysql.user where concat(user, '@', host) like (select CURRENT_USER()) because I didn't have privileges to view the table, but I was able to root around the INFORMATION_SCHEMA table and find what I needed. thanks! patrick On Tue, Jan 6, 2009 at 2:08 PM, Frank Flynn fr...@declan.com wrote: On Jan 6, 2009, at 7:41 AM, php-db-digest-h...@lists.php.net wrote: I was wondering if there is a php function and/or mysql function that can be run from php to find out the privileges for a particular db connection. Any ideas? Just to get you started try: select * from mysql.user where concat(user, '@', host) like (select CURRENT_USER())\G That worked for me, I'm sure you can go join to INFORMATION_SCHEMA and do more exotic (more precise) things too. Good Luck, Frank
Re: [PHP-DB] using database without mysql installed
I think I will have to use a second script on mysql server since (I forgot to mention) the database is replicated and that seems like the best option. Thanks for the help patrick On Sat, Oct 25, 2008 at 2:14 PM, Nitsan Bin-Nun [EMAIL PROTECTED] wrote: Umm you can convert it to files-based database (for instance - .sq3 - sqlite) and work with it, and yes, installing mysql after php is possible. HTH, Nitsan On Sat, Oct 25, 2008 at 4:32 PM, YVES SUCAET [EMAIL PROTECTED] wrote: Well, *technicaly*... you could open a socket to the MySQL server and talk to it, but that's probably a bit much, so... You never mentioned what kind of server this is. Is this on *nix or Windows? And do you have access to the php.ini file at least so you can enable the mysql extension? Other than that, I think your idea of putting a script on a second server to get your data would work just fine. You could have the second script even send back your requested data in XML or use Ajax to populate tables on the client-side rather than the server side. It may even make your application run more smoothly (and definitely scalable). HTH, Yves -- Original Message -- Received: Fri, 24 Oct 2008 03:35:47 PM CDT From: Patrick Price [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] using database without mysql installed I am trying to access a mysql database server from a php 5.2 server that doesn't have mysql installed on it. I don't have access or the ability to reinstall php on this server. Is it possible to install mysql after php is installed and up and running? If it is not possible, what would be the best solution to accessing the database? The best solution I could think of would be to require a php file that is running on a server that I have control of and that has mysql installed and using that file to call the database to get the information I need. Thanks for any help. Patrick -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] using database without mysql installed
I am trying to access a mysql database server from a php 5.2 server that doesn't have mysql installed on it. I don't have access or the ability to reinstall php on this server. Is it possible to install mysql after php is installed and up and running? If it is not possible, what would be the best solution to accessing the database? The best solution I could think of would be to require a php file that is running on a server that I have control of and that has mysql installed and using that file to call the database to get the information I need. Thanks for any help. Patrick