Page 1 of 2

More PHP & MySQL Help

Posted: Mon Jul 13, 2009 8:22 pm
by Foxy999
How can I read a table backwards? When new elements are added to my table I went them to be displayed first, so I need to read the table from the end to the beginning.

I think this has to do with my sql query and not my php code, this is what I have now "SELECT * FROM SELL"

Foxy

Re: More PHP & MySQL Help

Posted: Mon Jul 13, 2009 8:33 pm
by Ollie Saunders
Try "ORDER BY id DESC" or something like that.

Re: More PHP & MySQL Help

Posted: Mon Jul 13, 2009 8:44 pm
by Foxy999
That didn't help, I don't think there is anything in my database that will shows when the elements were added.

Re: More PHP & MySQL Help

Posted: Tue Jul 14, 2009 6:54 am
by Ollie Saunders
You just identified an important prerequisite.

Re: More PHP & MySQL Help

Posted: Tue Jul 14, 2009 3:38 pm
by Foxy999
Thanks I noticed last night that I should make some kind of system for this. I was wondering what would be the best way for doing this, here's my idea:

$filename = "id.txt";
$fd = fopen($filename, "r") or die("Can't open $filename");
$fstring=fread($fd, filesize ($filename));
$fstring++;

Then $fstring would be the variable added to the database for the id number.

$fd = fopen($filename, "w") or die("Can't open $filename");
$fcounted = $fstring;
$fout = fwrite ($fd, $fcounted);
fclose($fd);

Or I could code something that could check against the database each time to find a valid id, but I think the above way is better in my situation because the id number has to do more with when it was entered.

Also, how can I store filepaths in a database? Like /images/item/deadbody.jpg

Re: More PHP & MySQL Help

Posted: Thu Jul 16, 2009 8:27 am
by DaiLaughing
This looks like another situation where you need to understand databases before you try to do what you are doing. Has your table got a primary key? If so what is it? If it is a number is it auto-increment? If so then the suggestion by Ollie will do the job in one short line. If you answered no to many of those questions then you need to learn about databases from the start. It won't take long to get the basics.

http://blog.strugglewith.me.uk/?p=73 might get you started.

Re: More PHP & MySQL Help

Posted: Thu Jul 16, 2009 12:38 pm
by Ollie Saunders
Foxy999, all tables must have a primary key, this usually is a column in the table that is increased in value with each INSERT INTO. This property of a primary key is called auto_increment in MySQL and will help you with what you want to achieve. Seek Wikipedia for a good definition of a primary key. Often the primary key is called "id" for "tablenameId", which is why I used it in my example.

You don't need to do anything with files. A file is a way of storing data in a way that won't disappear any time soon (unlike a variable that's very temporal in nature). This idea is called persistence. Files are just one type of persistence. Another type of persistence is a database. Yep. This means you should be able to do everything you need to do here with just the database.

Re: More PHP & MySQL Help

Posted: Thu Jul 16, 2009 1:23 pm
by DaiLaughing
Not all primary keys are auto-increment though (well mine are but...). Or even numeric.

Re: More PHP & MySQL Help

Posted: Thu Jul 16, 2009 1:34 pm
by Ollie Saunders
Indeed.

Re: More PHP & MySQL Help

Posted: Thu Jul 16, 2009 1:40 pm
by califdon
Since Foxy999 was exploring the use of text files, perhaps she (?) doesn't intend to use a database. Usually it would be far better to use a database, but we haven't been told what the intention is, or the rationale. Foxy999, we have to resort to guessing what you're trying to do. If you'd like further help, we'll be glad to try, but you definitely need to explain more about what you want to accomplish.

Re: More PHP & MySQL Help

Posted: Fri Jul 17, 2009 2:03 pm
by Foxy999
Thanks that helped. I have one more question, if I do change my code to use a database to keep track of id's instead of a file, when I delete an element from my database does the id go away or just does it change to a new element? (when auto-increment is on)


Foxy

Re: More PHP & MySQL Help

Posted: Fri Jul 17, 2009 2:23 pm
by califdon
Foxy999 wrote:Thanks that helped. I have one more question, if I do change my code to use a database to keep track of id's instead of a file, when I delete an element from my database does the id go away or just does it change to a new element? (when auto-increment is on)

Foxy
OK, a database is a collection of tables. A table is a collection of records. A record is a collection of fields or columns. I don't know what an element is. If you delete a record, it's gone. Doesn't exist any more. If the record contains a field that has the auto-increment attribute, that value is never re-used. That's the way you want it to be. The only purpose of an auto-increment field is to insure that every record in the table has a unique identifier so it can be addressed without ambiguity. Such fields should ordinarily never even be exposed to the users. If you need an identifier to be used by users, don't use an auto-incrementing field, assign your own values the way you wish.

Re: More PHP & MySQL Help

Posted: Fri Jul 17, 2009 2:35 pm
by Ollie Saunders
when I delete an element from my database does the id go away or just does it change to a new element? (when auto-increment is on)
If you mean row (or record) by element then yes, it goes away. The id is part of the row.

Re: More PHP & MySQL Help

Posted: Fri Jul 17, 2009 2:43 pm
by Foxy999
that leads me to my next question, is it possible to read the table starting from row 2 or 3 when rows are being constantly changed and deleted?

Re: More PHP & MySQL Help

Posted: Fri Jul 17, 2009 4:00 pm
by Ollie Saunders
Yes. One of the things a good database system provides you with is something called ACID that ensure this is safe and possible.

Within the application itself you can sometimes get a problem called race conditions that are a similar concern. You can use transactions to solve this.