More PHP & MySQL Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Foxy999
Forum Commoner
Posts: 45
Joined: Sat Mar 21, 2009 11:50 am

More PHP & MySQL Help

Post 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
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: More PHP & MySQL Help

Post by Ollie Saunders »

Try "ORDER BY id DESC" or something like that.
Foxy999
Forum Commoner
Posts: 45
Joined: Sat Mar 21, 2009 11:50 am

Re: More PHP & MySQL Help

Post by Foxy999 »

That didn't help, I don't think there is anything in my database that will shows when the elements were added.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: More PHP & MySQL Help

Post by Ollie Saunders »

You just identified an important prerequisite.
Foxy999
Forum Commoner
Posts: 45
Joined: Sat Mar 21, 2009 11:50 am

Re: More PHP & MySQL Help

Post 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
DaiLaughing
Forum Commoner
Posts: 76
Joined: Thu Jul 16, 2009 8:03 am

Re: More PHP & MySQL Help

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: More PHP & MySQL Help

Post 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.
DaiLaughing
Forum Commoner
Posts: 76
Joined: Thu Jul 16, 2009 8:03 am

Re: More PHP & MySQL Help

Post by DaiLaughing »

Not all primary keys are auto-increment though (well mine are but...). Or even numeric.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: More PHP & MySQL Help

Post by Ollie Saunders »

Indeed.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: More PHP & MySQL Help

Post 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.
Foxy999
Forum Commoner
Posts: 45
Joined: Sat Mar 21, 2009 11:50 am

Re: More PHP & MySQL Help

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: More PHP & MySQL Help

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: More PHP & MySQL Help

Post 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.
Foxy999
Forum Commoner
Posts: 45
Joined: Sat Mar 21, 2009 11:50 am

Re: More PHP & MySQL Help

Post 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?
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: More PHP & MySQL Help

Post 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.
Post Reply