Public Function to retrieve data from 2 tables pdo

[ScoobyDont]

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

[Jacques1]

And your question is ... what?

[ScoobyDont]

Sorry if I was not clear enough

 

I would like the query to link and retrieve ALL the data from both 'users' and 'vehicle' tables so I can use the data to echo in areas of the site

[Jacques1]

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.

[Barand]

Your column names are ambiguous  and confusing. Can you show us your table structures and some sample data?

[ScoobyDont]

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

 

ANd this vehicle

 

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

 

 

 

 

 

 

 

[Jacques1]

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

[ScoobyDont]

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

[Jacques1]

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.

[ScoobyDont]

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

[Barand]

 

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.

[ScoobyDont]

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

[ScoobyDont]

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

[ScoobyDont]

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

[Barand]

Add those columns' names to the list of columns in your SELECT clause.

 

EDIT: You now have a bound parameter but there is nowhere to bind the parameter to. You need a WHERE clause in the query

Edited February 27, 2017 by Barand

[ScoobyDont]

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());
        }
    }

[ScoobyDont]

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;
	}
}

[Barand]

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.

[ScoobyDont]

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

[ScoobyDont]

Sorry just seen the mistake here and corrected it 

$db = $db = new PDO('mysql:host='.HOST.';dbname='.DATABASE.'', USER, PASSWORD);

[Barand]

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 February 27, 2017 by Barand

[ScoobyDont]

Ahh, right ok, got ya thank you