How would I write the sql query in php to get this result?

Question

First of all I am currently trying to learn php and I thought I would build a basic maintenance management app to better grasp everything I see in tutorials, and I hit a roadblock. I've been trying to get this to work following various tutorials online but I had no success so far, so I thought I would ask for help here. I really hope you can help me.

What I would like to know is how can I write the php & mysql to write the data from the first 3 table to the desired_table from below ?

Clients
------------------------------
client_id    | client_name
------------------------------  
    1        | client_1 
------------------------------  
    2        | client_2
------------------------------  
    3        | client_3 


Equipments
------------------------------
eq_id        | eq_name
------------------------------  
    1        | pc 
------------------------------  
    2        | laptop
------------------------------  
    3        | printer


Operations
------------------------------------------------
op_id        | op_desc
------------------------------------------------    
    1        | dust cleaning
------------------------------------------------    
    2        | changing processor cooling paste
------------------------------------------------    
    3        | cpu replacement
------------------------------------------------    
    4        | display replacement
------------------------------------------------    
    5        | dvd-rom replacement
------------------------------------------------    
    6        | ram replacement
------------------------------------------------    
    7        | cartrige replacement



Desired_table
-------------------------------------
id  | client_id  | eq_id  | op_id  |
-------------------------------------   
  1 |    1       |    1   |   1    |
-------------------------------------
  2 |    1       |    1   |   2    |
-------------------------------------   
  3 |    1       |    1   |   3    |
-------------------------------------   
  4 |    1       |    1   |   5    |
-------------------------------------   
  5 |    1       |    2   |   1    |
-------------------------------------   
  6 |    1       |    2   |   2    |
-------------------------------------   
  7 |    1       |    2   |   4    |
-------------------------------------   
  8 |    2       |    1   |   1    |
-------------------------------------   
  9 |    2       |    1   |   2    |
-------------------------------------   
  10|    2       |    1   |   3    |
-------------------------------------   
  11|    2       |    1   |   5    |
-------------------------------------   
  12|    2       |    3   |   1    |
-------------------------------------   
  13|    2       |    3   |   7    |

I thought I would have a form with input fields for the client data and the equipment data and operations data I would have in dropdowns. When I would select an equipment a new dropdown would appear with the operations, and then submit it.

Hope this doesn't get marked as too broad subject :)

This looks like a homework question to me.. ..have you tried anything? — MaggsWeb, Sep 22 '15 at 11:58
@MaggsWeb :) It's not homework. I am a former accountant trying to learn something new. I had a little experience with PHP from building WP themes and wanted to solidify my knowledge. Hence the 'trying to learn php' bit in my post. And yes I have been trying to get it to work for the past 3-4 days and it's very frustrating. I can't even concentrate on my other work because of this. — Cristian N, Sep 22 '15 at 12:08
So, add your code.. ..say where you think its going wrong.. ..and people will help. No one is going to do it all for you. — MaggsWeb, Sep 22 '15 at 12:09
@GhanshyamKatriya so I have been told, but no one really said how, which is kinda why I am asking the question in the first place, I can't figure it out on my own — Cristian N, Sep 22 '15 at 12:11
@MaggsWeb this is my project https://bitbucket.org/Kaladan/c , as for the other matter, I managed to follow several tutorials online and they got me this far, I know what I need my database to look like but don't know how to get there. I asked for code because I found it is easier for me to figure out the logic of a piece of code that than to figure out how to write it in the first place. I know it may not be the best way to learn to code but it works for me. — Cristian N, Sep 22 '15 at 12:45
If you're new to php starting with ajax like Ghanshyam suggested right away seems a bit to much. I would just start off by accessing the submitted form with $_POST['input_name'] in the target page. — Crecket, Sep 22 '15 at 13:50
@Crecket - I know how to pass the data into the first 3 tables and I know how to pull the data from those same tables. What I need to figure out is how do I push the data into the 4th table (desired_table) so that it looks like the one I showed above. For 1 client there will be multiple pieces of equipment, each with multiple maintenance operations assigned to it. Sure, I also think it can be done with AJAX but does that not ( hope I am not mistaken ) use the php file with the query that writes the data to the database ? — Cristian N, Sep 22 '15 at 14:14

score 0 · Answer 1 · edited May 23 '17 at 12:22

Using the code from your bitbucket this should do it:

$page = $db->prepare("
    INSERT INTO new_table_name (column_name1, column_name2, column_name3) 
    VALUES (:col1, :col2, :col3)
");

$page->bindparam(':col1', $value1);
$page->bindparam(':col2', $value2);
$page->bindparam(':col3', $value3);
$page->execute();
if($page->rowcount() > 0){
    echo "Inserted a new row";
}

Here is a example, replace 'new_table_name' to the name off the new table and change the column names.

After that simiply change $value1, 2 and 3 to the variables that contain the value you want to insert and you're good to go.

If you want to know if the insert was succesful just check with $page->rowcount(). If the rowcount is more than 0 it was succesful.

And just to clarify, this is called a prepared statement if you don't know this yet. What the code does is this:

Prepare tells the server what you're planning to do. This adds a really strong layer off protection since basic sql injection are close to impossible to do.
After that you bind the variables to the given parameters (Other syntax is possible, I just prefer this one). The code takes care off quotations so just put the parameter in there.
Execute the actual query

And just for good measure, you can add 2 extra parameters to bindparam. In the third you can define a variable type, PDO::PARAM_STR and PDO::PARAM_INT block any other type off variable. The 4th lets you set a max length, if the variable is longer it will get blocked.

This will look like this:

$page->bindparam(':col1', $value1, PDO::PARAM_STR, 100);
//block anything which isn't a string or longer than 100 characters

I will give your answer a shot. If I can make it work I will mark it as most helpful — Cristian N, Sep 23 '15 at 06:48
@Kaladan here is the official documentation if you need more help, I forgot to add it to the answer. [Link](http://php.net/manual/en/pdo.prepared-statements.php) — Crecket, Sep 23 '15 at 06:54

How would I write the sql query in php to get this result?

1 Answers1