Web Analytics Made Easy -
StatCounter Can one MySQL query hold two (select) ? - CodingForum

Announcement

Collapse
No announcement yet.

Can one MySQL query hold two (select) ?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Can one MySQL query hold two (select) ?

    I want to retrieve data from the two tables (employee_payroll, and employee_info) is that possible to do that with one query?

    PHP Code:
    $query mysql_query("

                        SELECT *
                        FROM `employee_payroll`
                        WHERE `branch` LIKE '%
    $keyword%'

                       AND

                        SELECT *
                        FROM `employee_info`
                        WHERE `employee_id` LIKE '%
    $id%'
                       
     "
    ); 

  • #2
    Do you want employees from a specific branch? If so then use a join.

    Sent from my GT-S7560M using Tapatalk

    Comment


    • #3
      Do not cross post. You have also posted this question in the php forum where it has received replies.

      Sent from my GT-S7560M using Tapatalk

      Comment


      • #4
        Yes, how do I use a join?
        And the topic seemed more relative here that's why I had to post it here, and your answer is more helpful than what I received there.

        Comment


        • #5
          1. We need the details of both tables to see their relationship so as to work out the join.

          2. mysql_query was removed from PHP in December 2015 (it was marked as obsolete in July 2013 so people were supposed to stop using it then) The two replacements mySQLi and PDO were both introduced in July 2004 so new code should have ceased using mysql_query about 10 years ago.

          3. You should use a prepare statement rather than a query so as to keep the SQL and data completely separate.
          Stephen
          Learn Modern JavaScript - http://javascriptexample.net/
          Helping others to solve their computer problem at http://www.felgall.com/

          Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

          Comment


          • #6
            Thank you
            The details are:

            Database Table (1) 'employee_payroll' with columns:
            employee_id
            branch_name
            absence_total
            attendance_total
            loan


            Database Table (2) 'employee_info' with columns
            employee_id
            first_name
            last_name
            address


            I would like to gather all employees that work in branch "cairo"along with their first_name and last_name from the (employee_info) table

            Comment


            • #7
              Using mySQLI the code would look something like this:

              PHP Code:
              $stmt $db->prepare('SELECT first_name, last_name FROM employee_payroll JOIN employee_info ON employee_payroll.employee_id = employee_info.employee_id WHERE branch_name=?'); 
              $stmt->bind_param('s''cairo');
              $stmt->execute();
              $stmt->use_result();
              $stmt->bind_result($first_name$last_name);
              while(
              $stmt->fetch_row() {
              echo 
              $first_name,' ',$last_name// replace this with the code to display the names
              }
              $stmt->free_result();
              $stmt->close(); 
              Using PDO would be similar but using slightly different commands.
              Stephen
              Learn Modern JavaScript - http://javascriptexample.net/
              Helping others to solve their computer problem at http://www.felgall.com/

              Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

              Comment


              • #8
                Since OP is using the same id names in both tables (My Preference) you can clean up the query with USING.


                BEFORE

                SELECT first_name, last_name FROM employee_payroll JOIN employee_info ON employee_payroll.employee_id = employee_info.employee_id WHERE branch_name=?'

                AFTER

                SELECT first_name, last_name FROM employee_payroll JOIN employee_info USING (employee_id) WHERE branch_name=?'
                To save time, lets just assume I am almost never wrong.

                The XY Problem
                The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

                Make A Donation https://www.paypal.me/KevinRubio

                Comment


                • #9
                  It didn't work
                  I used the following instead :

                  SELECT
                  *
                  FROM employee_payroll
                  LEFT JOIN employee_info
                  on employee_payroll.id = employee_info.id
                  WHERE employee_payroll.branch = 'Cairo';

                  I still have an issue with the code but I'll figure it out later.
                  Thanks

                  Comment


                  • #10
                    Originally posted by samiraljohani View Post
                    I still have an issue with the code
                    Of course you do - you are still using * that has no place in SQL and jumbling the SQL and data together.
                    Stephen
                    Learn Modern JavaScript - http://javascriptexample.net/
                    Helping others to solve their computer problem at http://www.felgall.com/

                    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

                    Comment


                    • #11
                      I would suggest you use PDO instead of Mysqli. You can start here: https://phpdelusions.net/pdo
                      To save time, lets just assume I am almost never wrong.

                      The XY Problem
                      The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

                      Make A Donation https://www.paypal.me/KevinRubio

                      Comment


                      • #12
                        lol I know there are many issues with my code but I just want to focus on my old style of coding until I get the code to function, after that I will enhance the code to work under php 7
                        Thank you guys for your help. I appreciate it.

                        Comment


                        • #13
                          This is your code and you are asking how to fix the window.


                          Click image for larger version

Name:	falling-down-house.jpg
Views:	1
Size:	50.8 KB
ID:	2270492
                          To save time, lets just assume I am almost never wrong.

                          The XY Problem
                          The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

                          Make A Donation https://www.paypal.me/KevinRubio

                          Comment


                          • #14
                            Originally posted by samiraljohani View Post
                            I just want to focus on my old style of coding until I get the code to function
                            So get in your time machine and go back to before July 2013 when your code became obsolete - or back to before July 2004 when it ceased to be the way to write new code.
                            Stephen
                            Learn Modern JavaScript - http://javascriptexample.net/
                            Helping others to solve their computer problem at http://www.felgall.com/

                            Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

                            Comment


                            • #15
                              Originally posted by samiraljohani View Post
                              Yes, how do I use a join?
                              And the topic seemed more relative here that's why I had to post it here, and your answer is more helpful than what I received there.
                              For future reference then, ask a moderator to move your thread to the different sub forum. That way you wont be getting answers in two different threads.

                              Sent from my GT-S7560M using Tapatalk

                              Comment

                              Working...
                              X