10 Replies Latest reply on May 27, 2010 1:30 PM by bregent

    Database Matching in Php

    Tony404 Level 1

      Hi I was wondering if any one could help me with a database matching code in php. I am trying to get the age and gender of a logged in user and matching it to another database that has pictures and content. I need the content echoed at the second database query to fit the users logged in age and gender.

       

      //Lets presume I have connected to the database and I have a session start enabled.

       

      $id=$_SESSIOIN['id'];

       

      //This uses the logged in session as a query to find out their age.

       

      $person=mysql_query("SELECT * FROM users  WHERE  id= '$id' ");

       

      while($row = mysql_fetch_assoc($person))

      {

       

      // collecting data from database and turning it into variables.

       

      $age=$row['age'];

       

      $gender=$row['gender'];

       

      }

       

      //This is a query to second database matching information from the first database

       

       

      $notel=mysql_query("SELECT * FROM content WHERE id=' ' , Ages='$age ', Genders='$gender' , pictures=' ', content=' '");

       

       

      while=($rows=mysql_fetch_assoc($notel))

      {

       

      //here I try to echo all the pictures or content that  matches the echoed users age  and gender from the first database.

       

      echo " $rows=['pictures'];

                $rows=['content'];";

       

      }

       

       

      }

       

      PLease br welcome to change the code. I believe I am doing some thing wrong because I get errors when debug. The erros I usually related with the second database notel while loop.

        • 1. Re: Database Matching in Php
          Ben M Adobe Community Professional

          A couple things I notice.  Do you use other variables from that first query on the page?  If not that is inefficient and you could optimize your query by using: SELECT age, gender FROM users WHERE id=$id. 

           

          Also with your second query are you really searching for null values in the id, pictures and content fields.  That just seems really odd.  I'm a little confused about what you are trying to get with that second query.  What values exist in those rows, what are you trying to gain here?  I have an idea, but I want to make sure I understand this 100% first.

          • 2. Re: Database Matching in Php
            Tony404 Level 1

            Hi

             

            The second database has pictures and content. All the pictures and content in second database have age and gender labels. So the plan is to find out the logged on users age and gender in the first database. Then to query the second database and only echo the pictures or content that fit the logged on users age and gender.

             

             

            I hope this makes a bit more sense.

            • 3. Re: Database Matching in Php
              Ben M Adobe Community Professional

              That's not quite what I was looking for but I think I have it right.  You only need to use one query:

               

              SELECT t1.age, t1.gender, t2.pictures, t2.content FROM users AS t1 INNER JOIN content AS t2 ON (t1.age = t2.age AND t1.gender = t2.gender) WHERE t1.id = $id;

               

              The one thing that I am still questioning is your second query where id, picture and content are = to null, or no value whatsoever.  I'm not sure why you had that in your second query and if those same columns existed in this content table as it does in the user table.  Again this will only select the age and gender from the users table and pictures and content from the content table because you did not state whether more rows are necessary for the page load and if they are unnecessary, depending on traffic and memory it is better practice to just select what you need when you need it rather than to return everything.  And actually if you don't need the age and gender printed on the page they could be removed from the select portion of the statement.

              • 4. Re: Database Matching in Php
                bregent Most Valuable Participant

                >All the pictures and content in second

                >database have age and gender  labels.

                 

                Are you really talking about a second database, or just a different table? In any case, you can do this all with a single query.

                • 5. Re: Database Matching in Php
                  Tony404 Level 1

                  Hi

                  Sorry I am talking about a second table. How can I do it in single query code?

                  • 6. Re: Database Matching in Php
                    Ben M Adobe Community Professional

                    Tony that would be the last post I gave you with the JOIN query.  The problem is I cannot figure out your second query from your original post where you are looking for items with blank values so that query might not have worked.  Could you draw up your database as a table or just let us know what the column names are for both tables?

                    • 7. Re: Database Matching in Php
                      Tony404 Level 1

                      Sorry for any miss interprartion.

                       

                      the blank spaces represent information I an not interested in outputting.

                       

                       

                      The main purpose of the code is to output data about the use and look at the advertisement table and output a picture or video that represents the user session.

                       

                      The blanks are data that I did`nt wish to output. I just want to make sure data outputed out of the second table matches the registered users age or gender.

                      I deeply hope you understand. The first table lets you know the age and gender of the user curently logged in. The second table uses the data  from the first table to output image and video data that fits the use age and gender that had benn collected from the first table.

                       

                       

                      if you have code to simplify this process please feal free to supply.

                      • 8. Re: Database Matching in Php
                        Freakcircuit Level 1

                        Sorry I meant two tables. The first holds the current users age and gender.

                         

                        The second table holds content that is label for users of a particular gender and age. So I am trying to match the age and gender of the current user to

                        content in the second table that has labeled ages and gender connected to it.

                         

                        I hope this makes sense.

                        • 9. Re: Database Matching in Php
                          Freakcircuit Level 1

                          Thank you for the hint.

                           

                           

                          Could you please provide the same code as if I was using the mysql_query so that I can understand it much clearly. It seems to be the stuff I am looking.

                           

                          Hope its not a bother.

                          • 10. Re: Database Matching in Php
                            bregent Most Valuable Participant

                            >if you have code to simplify this process please feal free to  supply.

                             

                            SnakeEyez02 gave you the SQL to perform this. Look back a few posts to find it in this thread.