Page 1 of 1
Adding and Retrieving data to mySQL
Posted: Wed Mar 16, 2005 6:46 pm
by emilsoft
Hello everyone, I've just started working with mySQL and due to my very limited knowledge of databases... I cannot figure out what code to write to accomplish the following tasks, even after hours of reading tutorials.
Here's what I'd like to do... I have created a simple table on my mySQL server with the following query:
Code: Select all
CREATE TABLE test_table (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
typeid2 INTEGER UNSIGNED NULL,
nametext TEXT NULL,
linktext TEXT NULL,
PRIMARY KEY(id)
);
Now What I'm trying to do is have a PHP page that'll add only up to 4 rows of data to the table (i.e. I don't want 'id' to contain more than 4 rows of values) and after that I'll have another PHP page that'll read the values from the table and list them in different HTML tables.
What I can't figure out how to do is how to add info to the DB and then read them later seperately (this might be a bit easier, since I've got a fixed number of 4 rows).
Could anyone please give me an example code that would accomplish the tasks that I want?
Thanks, any help would be greatly appreciated.

Posted: Wed Mar 16, 2005 7:24 pm
by feyd
Code: Select all
mysql_query('INSERT INTO `test_table` (`typeid2`, `nametext`, `linktext`) VALUES(\'3\', \'some text\', \'hey, some more text\')') or die(mysql_error());
Code: Select all
$query = mysql_query('SELECT * FROM `test_table` LIMIT 4') or die(mysql_error());
for info:
- INSERT Syntax
- SELECT Syntax
Posted: Wed Mar 16, 2005 7:45 pm
by emilsoft
Thanks for you reply. I managed to write the code to get the DB values successfully.... But when I try to insert data... it keeps adding it after the previous values, thus incrementing 'id' past 4 rows. Is there any way to keep 'id' limited to 4 values and then everytime a new value is being added, it goes to the top? In other words....
Let's say I have 4 rows in my table, now I'm going to add a new one to it, I want it to...
1. Delete the 4th row.
2. Push the 3rd row to the 4th row
3. Push the 2nd row to the 3rd row
4. Push the 1st row to the 2nd row
5. Add the new row values in place of the first row
Is this possible? If yes, how can I implement it?
Thanks again.

Posted: Wed Mar 16, 2005 8:05 pm
by feyd
you can use update to iteratively move the rows logically. Your selection must use ORDER BY.
The update will need to use ORDER BY as well (in reverse of the selection) .. it could be recommended to remove the auto_increment setting on the id, since you aren't actually allowing them to keep incrementing.
It can alternately be recommended that you should not delete any records, and instead just select from the table using ORDER BY to only grab the last 4 records.
Posted: Wed Mar 16, 2005 8:14 pm
by emilsoft
feyd wrote:
It can alternately be recommended that you should not delete any records, and instead just select from the table using ORDER BY to only grab the last 4 records.
That would be perfect... could you give me a small one line example on how to add a row and retrieve it later on using ORDER BY?
Thanks alot.

Posted: Wed Mar 16, 2005 8:23 pm
by feyd
try taking a stab at it first..

Posted: Wed Mar 16, 2005 8:27 pm
by emilsoft
feyd wrote:try taking a stab at it first..

lol, ok... BTW, can I contact you on MSN?
Posted: Wed Mar 16, 2005 9:10 pm
by mudvein
here is a nice article for you to read on the subject. Onlamp has some of the best articles on the net for almost anything reguarding php & mysql.
(I guess it would help if I posted the link)
http://www.onlamp.com/pub/a/php/2004/01 ... tions.html
Posted: Wed Mar 16, 2005 9:37 pm
by painperdu
You want the newest entry to be the lower number?
In other words, you want the newest entry to display the lower number when it's pulled out?
I'm not sure I understand but if this is what you are trying to do then just assign numbers when you take the rows out.
O.k., I just re-read what you're trying to do. Do you mean you wish to paginate the results ala google?
Posted: Wed Mar 16, 2005 10:39 pm
by emilsoft
painperdu wrote:
O.k., I just re-read what you're trying to do. Do you mean you wish to paginate the results ala google?
No, that wasn't my initial goal, but now that I have developed my webpage further... I'm trying to figure out how to paginate the results (preferably into 10 entries per page)