When I open a MySQL connection in PHP with just PHP's built-in MySQL functions, I do the following: $link = mysql_connect($servername,$username,$password); mysql_select_db($dbname); //queries etc. mysql_close($link); When I open a connection with PDO, it looks like this: $link = new PDO("mysql:dbname=$dbname;host=$servername",$userna...
I have a database of users. I would like to create a graph based on userbase growth. The query I have now is: SELECT DATE(datecreated), count(*) AS number FROM users WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <= DATE(NOW()) GROUP BY DATE(datecreated) ORDER BY datecreated ASC This returns almost what I want. If we ge...
Im writing a pruning script, to delete content from my site that was uploaded over a week ago, and been accessed 0 or 1 times, also in the last week. there are 2 tables: daily_hits - which stores the item id, date, and number of hits that item got on that date. videos - stores actual content I came up with this. $last_week_date =...
SELECT webcal_entry.cal_id, webcal_entry.cal_name , webcal_entry.cal_priority, webcal_entry.cal_date , webcal_entry.cal_time , webcal_entry_user.cal_status, webcal_entry.cal_create_by , webcal_entry.cal_access, webcal_entry.cal_duration , webcal_entry.cal_description , webcal_entry_user.cal_category FROM webcal_entry, webcal_entry_user ...
10.12.1 10.12.10 10.12.11 10.12.12 10.12.13 10.12.14 10.12.15 10.12.16 10.12.17 10.12.18 10.12.19 10.12.2 10.12.3 10.12.4 10.12.5 10.12.6 10.12.7 10.12.8 10.12.9 This is a varchar column. But '10.12.19' is the max data i need. How can i get it? ...
I'm creating a SQL query with PDO in PHP. Unfortunately, there seems to be an error in my query. I've tried $query->errorInfo(), but that only gives me a little bit of the error message ("There is an error in your syntax near..."). How can I display the entire query that PDO is passing to my database? TIA. ...
Hi there! I have a problem that I can't figure out myself. I've tried using LEFT JOIN etc but nothing seems to work. I'm using MySQL so you know. I'm building a little blogportal for me and my friends and all users have their own blog. database: users: id, username, password, etc blog: id, title, text, user_id, etc relations fo...
i have a table in database that is having 7k plus records. I have a query that searches for a particular id in that table.(id is auto-incremented) query is like this-> select id,date_time from table order by date_time DESC; this query will do all search on that 7k + data.......isn't there anyways with which i can optimize this query ...
When I have a text field, I generally store it as varchar. But then I encounter the issue of how do I know the limit I should place? Estimating how much text a user will type seems very imprecise. As varchar uses as much space as needed, is it better to set the limit to far greater than you estimate? Is there any disadvantage to using ...
I am having a basic mysql stored procedure for inserting user data along with email id. For example CALL INSERTUSER( 'Someone', '[email protected]' ); My problem is that the stored procedure is taking @ as special character.I am new to mysql. How can I insert this? Is there any escape character?Please help. ...
Currently the slave often falls several thousand seconds behind master,which is a real headache! ...
I'm using the PHP Doctrine ORM to build my queries. However, I can't quite seem to figure how to write the following WHERE clause using DQL (Doctrine Query Language): WHERE name='ABC' AND (category1 = 'X' OR category2 = 'X' OR category3 = 'X') AND price > 10 How can I specify where the parentheses go? What I currently have in my PH...
I have a database where most tables have a delete flag for the tables. So the system soft deletes items (so they are no longer accessible unless by admins for example) What worries me is in a few years, when the tables are much larger, is that the overall speed of the system is going to be reduced. What can I do to counteract effects l...
How do I see what the character set that a MySQL database, table and column are in? Is there something like SHOW CHARACTER SET FOR mydatabase; and SHOW CHARACTER SET FOR mydatabase.mytable; and SHOW CHARACTER SET FOR mydatabase.mytable.mycolumn; ...
I have two tables, one for routes and one for airports. Routes contains just over 9000 rows and I have indexed every column. Airports only 2000 rows and I have also indexed every column. When I run this query it can take up to 35 seconds to return 300 rows: SELECT routes.* , a1.name as origin_name, a2.name as destination_name FROM ro...
I'm trying to copy the contents of a column in one mysql database to an identical table in another mysql database. I'm using: UPDATE db1.table SET db1.table.name = db2.table.name, db1.table.address = db2.table.address WHERE db1.table.id = db2.table.id; I'm getting the error 1054: Unknown column 'db2.table.id' in 'where cl...
I have three tables called: users, facilities, and staff_facilities. users contains average user data, the most important fields in my case being users.id, users.first, and users.last. facilities also contains a fair amount of data, but none of it is necessarily pertinent to this example except facilities.id. staff_facilties consists ...
Can I somehow ignore the post with the earliest date? With SQL or PHP? SELECT subject, date FROM posts WHERE id = $id ORDER BY date DESC while ($row = mysql_fetch_array($query)) { Cheers ...
With regards to SQL and queries, whats the difference between an in-memory table, temp table and a pivot table? ...
The PROBLEM I have a query like this: select a.id from a join b on ( a.id = b.my_a ) join .... where ( /* complex and expensive conditional */ ) AND (( /* conditional #1 */ ) OR ( /* conditional #2 */ ) OR ( /* conditional #3 */)) I would like to have the query return something like: select a.id, co...