I want to store the last X records in an MySQL database in an efficient way. So when the 4th record is stored the first should be deleted. The way I do this not is first run a query getting the items. Than check what I should do then insert/delete. There has to be a better way to do this. Any suggestions? Edit I think I should ad...
Hi, I am using a table which has Createdon and Lastmodifiedon fields, I have to display first the newly added record based on createdon and then followed by lastmodifiedon records using mysql. I have tried adding "ORDER BY Createdon,Lastmodifiedon desc" at the end of the query;but sorting based on first column only occurs. Anyone please ...
I am create a class which uses PDO to interact with MySQL. Can I create a new MySQL table using PDO? ...
Working on an AJAX website (HTML,CSS,JavaScript, AJAX, PHP, MySQL). I have multiple javascript functions which take rows from mysql, wrap them in html tags, and embed them in the HTML (the usual usage of AJAX). THE PROBLEM: Everything is working perfect, except when I run the site with Firefox (for once its not InternetExplorer causin...
Hi there, I'm not sure if this is possible what I'm trying to achieve. I want to get the avg of averaged columns. SELECT avg(col1), avg(col2), avg(col3) FROM tbl My Result should be the avg of all three avg columns, is this possible? Something like this SELECT avg( col1, col2, col3) FROM tbl doesn't work at MySQL 5.1 ...
I need to find the latest location of each cargo item in a consignment. We mostly do this by looking at the route selected for a consignment and then finding the latest (max) time entered against nodes of this route. For example if a route has 5 nodes and we have entered timings against first 3 nodes, then the latest timing (max time) wi...
I have a huge table that is mainly used for backup and administrative purposes. The only records that matters is the last inserted record. On every hit to order by time inserted is just too slow. I want keep a separate table with the last inserted id. In PHP I now insert, get last inserted id, and update the other table. Is there a m...
Hello. Can anyone tell me how to insert special characters into a MySQL database? I've made a PHP script which is meant to insert some words into a database, although if the word contains a ' then it wont be inserted. I can insert the special characters fine when using PHPmyAdmin, but it just doesn't work when inserting them via PHP. Cou...
Is it possible to log CREATE / ALTER statements issued on a MySQL server through phpMyAdmin? I heard that it could be done with a trigger, but I can't seem to find suitable code anywhere. I would like to log these statements to a table, preferably with the timestamp of when they were issued. Can someone provide me with a sample trigger t...
I just wrote a stored function to calculate the working days between two dates. This works select CountWeekDays('2010-03-07','2010-04-07') This doesn't work select CountWeekDays(o.order_date,o.created_date) from orders o; Any idea how to make this one work ?? function definition delimiter $$; CREATE FUNCTION ...
When I use Max to find the maximum value of a field in a particular MySQL Group after using GROUP BY, is it possible to get the entire row which contains the maximum value? ...
How do I build a connection string which includes a passsword having a "=" in it? (I'm connecting to MySql 5.1) For example, let's say the password is "Ge5f8z=6", what would the connection string look like? I tried: Server=DBSERV;Database=mydb;UID=myuser;PWD="Ge5f8z=6"; and Server=DBSERV;Database=mydb;UID=myuser;PWD=Ge5f8z=6;" Bo...
Looking at this query there's got to be something bogging it down that I'm not noticing. I ran it for 7 minutes and it only updated 2 rows. //set product count for makes $tru->query->run(array( 'name' => 'get-make-list', 'sql' => 'SELECT id, name FROM vehicle_make', 'connection' => 'core' )); while($tempMake = $tru->query->...
I have a lot of SQL queries like this: SELECT o.Id, o.attrib1, o.attrib2 FROM table1 o WHERE o.Id IN ( SELECT DISTINCT Id FROM table1 , table2 , table3 WHERE ... ) These queries have to run on different database engines (M...
I have a table with (essentially) 3 columns - name, votes and rank. I want to order the table by votes and then update 'rank' to reflect this order, so that the one with the most votes will have rank set to 1, the second most votes to 2 etc. I can do this in PHP but it seems pretty wasteful - is there a way to do this with one SQL quer...
I am doing a SQL Insert to populate my table. I have a unique generated ID in one table that I would like to use in another table for my join. Is this possible? .NET MVC -- using (SqlConnection connect = new SqlConnection(connections)) { SqlCommand command = new SqlCommand("ContactInfo_Add", connect); command.Parameters.Add(n...
The SQL query without where statement runs great and outputs good results, but when I include WHERE condition it shows Unknown column 'date1' in 'where clause'. What's the problem? SELECT IF( e.weekly, DATE_ADD(DATE(e.time), INTERVAL CEIL(DATEDIFF('2010-04-08', e.time)/7) WEEK ), DATE(e.time)) AS `e.date1`, `v`.`lat`...
i have a html page in which i enter data which then submits and inserts in a database on a php page. how would i validate in php that the data received is not a duplicate of the data in the database? any help appreciated. ...
Hello all, Both the MySQLi and MySQLi_STMT classes have an $insert_id property. If I am connected to my database using a MySQLi object (say $db), and then I perform an INSERT with a MySQLi_STMT object (say $stmt), to get the id of the last INSERT, should I use: $last_id = $db->insert_id; or $last_id = $stmt->insert_id; Or would ...
I'm storing a huge set of songs in a MySQL database. This is what I store in the 'songs' table: CREATE TABLE `songs` ( `song_id` int(10) unsigned NOT NULL auto_increment, `song_artist` varchar(255) NOT NULL, `song_track` varchar(255) NOT NULL, `song_mix` varchar(255) NOT NULL, `song_title` text NOT NULL, `song_hash` varchar(...