Jump to content

Public Function to retrieve data from 2 tables pdo


ScoobyDont

Recommended Posts

Hi,

I am at the minute changing from mysql to pdo and whilst its starting to click I am still struggling with a few things namely trying to retrieve and join data from 2 separate tables

 

Table 1: 

Name: users

columns  1.user_id

               2.registration

               3. email

 

Table 2:

Name vehicle

columns 1.reg_id

              2. registration 

              3.vehicle

              4.chassis

 

The function I am currently trying to get to work is this

public function UserDetails($user_id)
    {
        try {
            $db = DB();
            $query = $db->prepare("SELECT user_id, registration, email FROM users INNER JOIN vehicle 
                                    ON users.user_id = vehicle.reg_id");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            if ($query->rowCount() > 0) {
                return $query->fetch(PDO::FETCH_OBJ);
            }
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
    }

I want the data so I can <?php echo $user->Vehicle?> on the frontend

 

Any help much appreciated

Link to comment
Share on other sites

I know. But what are you struggling with? I can try to guess what your problems are, but I suggest you simply tell us.

 

You seem to have forgotten the WHERE clause. Fixing this will also fix the nonexistent parameter in the bindParam() call. I also wonder how you're going to deal with (or prevent) multiple vehicles.

Link to comment
Share on other sites

Right OK, sorry will try and explain what I am trying to do in a little more detail

 

On the registration form the user enters 

 

1, Registration

2. Email

3. Vehicle

4 Chassis No

 

this data is then passed into two tables , 1&2 insert into 'users' and 3&4 inserts into 'vehicle' (I will be building on the data in these two tables, just need to link them first and retrieve the data

 

This is the users table

post-203324-0-55710100-1488127267_thumb.jpg

 

ANd this vehicle

post-203324-0-05467400-1488127268_thumb.jpg

 

I put registration in both as I thought this may be used to help create a relationship between the 2 but not sure how to implement it, hence the post

 

So my question is how do I query the two tables and retrieve all the info from the tables and that the vehicle details belong to the right user

 

Hope this is a little clearer of what I am trying to achieve

 

 

 

 

 

 

 

Link to comment
Share on other sites

What's the point of creating two tables when you want a strict one-to-one relationship?

Like mentioned I want to put a lot more columns in both 'users' and 'vehicle' and I wanted it to look a bit cleaner

 

Your response gives the impression that what I am trying to do is impossible..

Link to comment
Share on other sites

Many things are “possible”. But programming is more about what's sensible.

 

Your entire code is based on the assumption that there's a one-to-one relationship between users and cars. A user has exactly one car, and a car belongs to exactly one user. That's what a single table represents. Sure, you can also split the data into multiple tables, create an artificial one-to-one association via constraints, use joins to put all data back together and get hopelessly confused in the process. But what's the point of that?

 

If users and cars were in fact separate entities (e. g. a user can have multiple cars), two tables would be appropriate. But you don't have that.

Link to comment
Share on other sites

Right thank you for your advice, and without sounding rude, I wanted help with my function, not the structure of my databases, like already said I am just finally moving away from mysql and entering the world of pdo and to me it seemed like I have just learnt French but now I have to learn German.

 

So not only do I want help with the query it would also be a learining curve for me and maybe others to understand how to join multiple tables and retrieve related data from each.

 

Most forums expect you to try/search and smash your pc up before you ask the question, which is what I have done, 

 

So going back to my initial question:-

how do I query the two tables and retrieve all the info from the tables and that the vehicle details belong to the right user????

 

 

Thanks in advance

Link to comment
Share on other sites

 

I put registration in both as I thought this may be used to help create a relationship between the 2

 

Then that is what you should use for the join with your current tables.

SELECT ....
FROM user u
JOIN vehicle v ON u.registration = v.registration

However, a more flexible model would be ...

+-------------+
|   user      |
+-------------+
|  user_id    |--------+
|  email      |        |         +--------------+
|  etc        |        |         |  vehicle     |
+-------------+        |         +--------------+
                       |         | vehicle_id   |
                       +--------<| user_id      |
                                 | registration |
                                 | vehicle      |
                                 | chassis_no   |
                                 +--------------+

.. which easily allows you to add a second (and third...) car for a user just by adding another row with the user's user_id. In this model the join would be on the user_id columns.

Link to comment
Share on other sites

Thanks Barand for your advice, without boring you with the whole detail of the site I am trying to create I have decided to go with registration, as this is the unique to both vehicle and user the login page requires a registration and email to log in so it should not be possible for the owner to have two vehicles with the same reg no

 

If like Jacques1 your are thinking why not put it all into 1 table then in the perfect world for me this is what I would do but I cant, I am hoping to take data from other databases whos main unique identifier will be the registration so I have to go what ALL databases will have in common to a specific user which will be the Registration No

 

Thanks again I will have a try at a couple more queries and if I get stuck I will get back for more advice

Link to comment
Share on other sites

Right a few more hours trying and I am getting now where fast..Is it the rest of the function ok

 

This is where I am at at the minute

 public function UserDetails($user_id)
    {
        try {
            $db = DB();
            $query = $db->prepare("SELECT user_id, registration, email FROM users JOIN vehicle
				   ON vehicle.registration = users.registration");
	    $query->bindParam("user_id", $user_id, PDO::PARAM_STR);					  
            $query->execute();
            if ($query->rowCount() > 0) {
                return $query->fetch(PDO::FETCH_OBJ);
            }
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
    }

If I jiggle it I can retrieve the Registration number from users table but cant retrieve the vehicle and chassis number from the vehicle table

 

Any help is much appreciated

Link to comment
Share on other sites

Right a few more hours trying and I am getting now where fast..Is it the rest of the function ok

 

This is where I am at at the minute

 public function UserDetails($user_id)
    {
        try {
            $db = DB();
            $query = $db->prepare("SELECT user_id, registration, email FROM users JOIN vehicle
				   ON vehicle.registration = users.registration");
	    $query->bindParam("user_id", $user_id, PDO::PARAM_STR);					  
            $query->execute();
            if ($query->rowCount() > 0) {
                return $query->fetch(PDO::FETCH_OBJ);
            }
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
    }

If I jiggle it I can retrieve the Registration number from users table but cant retrieve the vehicle and chassis number from the vehicle table

 

Any help is much appreciated

Link to comment
Share on other sites

Sorry I have just sussed it using this 

public function UserDetails($user_id)
    {
        try {
            $db = DB();
            $query = $db->prepare("SELECT users.user_id, users.registration, users.email, vehicle.reg_id, vehicle.vehicle, vehicle.registration, vehicle.chassis
        FROM users, vehicle WHERE users.registration = vehicle.registration");				  
            $query->execute();
            if ($query->rowCount() > 0) {
                return $query->fetch(PDO::FETCH_OBJ);
            }
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
    }
Link to comment
Share on other sites

Right OK now I have sussed this and thank you again Barand for pointing me in the right direction, 

 

What if I have 2 tables with 30 columns each do I have to use shorthand or is there another way 

when I followed an old tutorial a few years ago and I used this function to get all the data without having to write all the columns names etc etc 

 

Would this or could this still work

function user_data($user_id) {
	$data = array();
	$user_id = (int)$user_id;
	
	$func_num_args = func_num_args();
	$func_get_args = func_get_args();
	
	if ($func_num_args > 1) {
		unset ($func_get_args[0]);
		
		$fields = '`' . implode ('`, `', $func_get_args) . '`';
		$data = mysql_fetch_assoc(mysql_query("SELECT $fields FROM `register` WHERE `id` = $user_id"));
		return $data;
	}
}
Link to comment
Share on other sites

It was better as it was. Just add a WHERE clause to tell it which user.

    $query = $db->prepare("SELECT users.user_id
                            , users.registration
                            , users.email
                            , vehicle.reg_id
                            , vehicle.vehicle
                            , vehicle.registration
                            , vehicle.chassis 
                            FROM users JOIN vehicle
                                ON vehicle.registration = users.registration
                            WHERE users.user_id = :user_id");
    $query->bindParam("user_id", $user_id, PDO::PARAM_STR);                      

It looks like the line

$db = DB();

is connecting to your database. Move that code out of the function and pass $db as a parameter to your function with $user_id. Connecting is a relatively slow process and you don't want to do every for every query.

Link to comment
Share on other sites

Right OK do you mean like this

public function UserDetails($user_id)
    {
        try {
            $db = $db = new PDO('mysql:host='.HOST.';dbname='.DATABASE.'', USER, PASSWORD);
            $query = $db->prepare("SELECT users.user_id
                            , users.registration
                            , users.email
                            , vehicle.reg_id
                            , vehicle.vehicle
                            , vehicle.registration
                            , vehicle.chassis 
                            FROM users JOIN vehicle
                                ON vehicle.registration = users.registration
                            WHERE users.user_id = :user_id");
    $query->bindParam("user_id", $user_id, PDO::PARAM_STR);			  
            $query->execute();
            if ($query->rowCount() > 0) {
                return $query->fetch(PDO::FETCH_OBJ);
            }
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
    }

And can I ask 1 more question about my function do I remove this catch (PDOException $e) when it goes live

Link to comment
Share on other sites

In what way is that "outside the function"?

$db = DB();                                         // connection OUTSIDE the function

$vehicle_data = UserDetails($db, $user_id);                        // pass the $db connection as a parameter

plus you need to redefine the function to accept the extra parameter

function UserDetails ($db, $user_id) {
   // function code
}
Edited by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.