Querying MySQL with PHP: How to Select Certain Data to Display?

Databases
1

As php/mysql practice, I’m creating a basic simpet engine (if that’s what you’d call it).

I’ve got most of it worked out but one problem I ran into is how to select and display certain data from a table in mysql.

Here’s what my table looks like:

Table name: pets

id - owner
1 - KenKenderson
2 - chocolat
3 - KenKenderson
4 - KenKenderson
5 - chocolat

The ‘id’ is unique for every pet and is also set up as the primary key. The ‘owner’ I set up as a fulltext index.

What I want to happen is this:
When a user visits their own profile, a query will be sent to mysql searching the owner column for their username. For every row containing their username, the profile will echo an img tag corresponding to the individual pet (for instance, 1.jpg if the first row is called).

So if KenKenderson logs in, the profile page will display three images, 1.jpg, 3.jpg, and 4.jpg. When chocolat logs in, it will display 2.jpg and 5.jpg.

My main problem is that I’m not sure how to go about setting up the query.

The only option I was able to think of was to go through the id #'s one by one, checking the owner column, printing or not printing, and then moving on to the next id #. This way is feasible and I know I can program it, but I was wondering if there was a faster, more feasible way to go about it? For instance, what if there were 200 rows? 1000 rows? 10,000 rows? As the numbers grow, is that search method still a viable one?

I’ve gone through the book I am using to teach myself many times looking for an answer, have done a lot of googling, and browsed a half dozen pages of thread titles on here with no luck. Any help and/or advice would be appreciated :slight_smile:

2

The owners table should be its own separate table responsible for holding information about all users within the system on a generic level.

The relationship that occurs between a pet and owner depends upon how you want to set up the system. This is based on whether or not a pet can be owned by a single person or many people.

If a pet can only belong to a single person then all you need is another table called pets with a foreign key to the owner. The foreign key will be responsible for linking the pet to its said user/owner.

The second option is that a pet can belong to two people. In this case what your looking at is a many to many relationship which will require two more tables. The first table will be a generic pets table that contains all the pet data without any type of foreign key to a user.

Instead the relationship should be factored out into a another table that contains a user and pet primary key. This way you can now support a pet belonging to two people, such as a husband and wife?

Given those two scenarios you should be looking at a database schema that looks more like the below.

users

  • id (primey key)
  • first_ name
  • last_name
    …

one to many

pets
id (primary key)
owners_id (user primary key)
…

many to many:

pets
id (primary key)
…

users_to_pets
– owners_id (user primary key)
– pets_id (pet primary key)
– primary key(owners_id,pets_id)

So once you structure your tables accordingly pulling the information your after will be a much more efficient and straight forward process.

3

First, thanks for the feedback!

Second, I do actually have a users table with their id being the primary key.

I turned the owner column in the pets table into an int and added a foreign key linking to the id column in the users table at your suggestion.

So here’s what the tables look like:

Users:
id (primary key) - name
1 - KenKenderson
2 - chocolat

Pets:
a_id (primary key) - owner (foreign key)
1 - 1
2 - 2
3 - 1

I then instituted this query and it worked great!

if (isset($_SESSION['user_id'])) {

  $sql = "SELECT id,name,lastactive FROM users WHERE id='".$_SESSION['user_id']."'";
  $query = mysql_query($sql);
  $row = mysql_fetch_object($query);
  $dbid = htmlspecialchars($row->id);
  $username = htmlspecialchars($row->name);
  $lastactive = htmlspecialchars($row->lastactive);

echo "<h1>$username</h1>";
echo "<p>Last active on: $lastactive</p>";

//display pets

$sql = "SELECT a_id,owner FROM pets WHERE owner = $dbid";
$query = mysql_query($sql);
$count = mysql_num_rows($query);
$i = 1;
while($row = mysql_fetch_object($query)) {
 $a_id = htmlspecialchars($row->a_id);
 $owner = htmlspecialchars($row->owner);
 if($owner == $dbid) {
  echo "<img src=\\"".$a_id.".jpg\\">";
 }
 $i++;
}

}

This code prints out the username, last active date, and pets of the logged in user.

Thanks so much for your help!