Hey everyone.
I'll go ahead and give you all some background on what I'm doing. I'm putting together a simple investment portfolio, and I'm sort of stuck trying to visualize how I should be setting up my database.
It's currently creating a table for the user upon registration, with provisions for 15 Stocks, being stock1name, stock1symbol, and stock1shares, incrementing to 15.
I'm thinking it would be simpler to not set up the full table for the user upon registration. It should be faster to add and drop rows for the user on-demand than to constantly search for an empty row and attempt to fill it.
My question is this:
How would I go about making this happen, as far as incrementing the rows?
For example,
Users adds stock 1. Script queries SQL to add the row for stock 1 including stock1name, stock1symbol, and stock1shares.
User completes stock 1, and now wants to add stock 2. How do I make the script increment the number for the next row?
Thanks in advance for any and all consideration.
Confused about how my database should be set up.
Moderator: General Moderators
-
Gulleysaurus
- Forum Newbie
- Posts: 2
- Joined: Sun Apr 17, 2011 7:30 pm
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Confused about how my database should be set up.
You are thinking like a spreadsheet, not a database. In databases there is no "order" or "next row." The records are in the table and you select the ones you want by some criteria and you can sort those records by some criteria. Most database have some way of assigning a unique ID to each row. Usually it is an incrementing value help in a sequence or as an autoincrementing value in the table. But the user never needs to see the unique IS, it is only there to allow you to specify that record individually to update or delete it.
Any kind of order you want to present the stocks in would by done by the presentation code after it had received the sorted records from the database.
Any kind of order you want to present the stocks in would by done by the presentation code after it had received the sorted records from the database.
(#10850)
-
Gulleysaurus
- Forum Newbie
- Posts: 2
- Joined: Sun Apr 17, 2011 7:30 pm
Re: Confused about how my database should be set up.
Thanks for your reply.
So from what I gather, you're telling me that the method of adding new rows as required isn't going to work.
I've been trying to detect whether or not there is any data in the pre-defined user table using the same method I use to check whether a username is or is not in use, but to no avail. Is it possible to use a wildcard to check for data existence in a row?
Here's what I've been attempting to use in order to do so:
This is now working as intended. I commented what I removed.
I'm still confused though, because there has got to be a cleaner way to accomplish this.
So from what I gather, you're telling me that the method of adding new rows as required isn't going to work.
I've been trying to detect whether or not there is any data in the pre-defined user table using the same method I use to check whether a username is or is not in use, but to no avail. Is it possible to use a wildcard to check for data existence in a row?
Here's what I've been attempting to use in order to do so:
This is now working as intended. I commented what I removed.
Code: Select all
$res = mysql_query("SELECT * FROM ".$_SESSION['username']." WHERE `stock1name` = *");
//$num = mysql_num_rows($res);
$num = mysql_fetch_row($res);
if($num > 1){ echo("Data found"); }
else { echo("Data not found"); }
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Confused about how my database should be set up.
You don't want a table per user. You want one table with a field with the username. Then select the records for that user.
Code: Select all
$res = mysql_query("SELECT * FROM stocks WHERE username='" . mysql_real_escape_string($_SESSION['username']) . "'");
$num = mysql_num_rows($res);
if($num > 1){ echo("Data found"); }
else { echo("Data not found"); }
(#10850)