9 Replies Latest reply on Aug 4, 2008 8:23 AM by Newsgroup_User

    PHP & MySQL recordset question

    9thReg Level 1
      Hello all,

      I've been searching for a while, and I can't seem to find an answer, so sorry if this has been asked a lot.

      I've got 5 tables that have similar information (id, firstname, lastname, topic, thread, and date).

      What I'm trying to do is have one search box where the user types in a keyword. Then it searches all 5 tables in both the topic and thread collumns. Then it displays the results from all 5 tables and orders them by date.

      I'm using dreamweaver 8, PHP, and MySQL.

      I've tried to use this format:

      SELECT column1 column 2
      FROM table1
      WHERE thread OR topic LIKE %variable%
      UNION
      SELECT column 1 column2
      FROM table 2
      WHERE thread OR topic LIKE %variable%
      ORDER by date DESC

      but keep getting error messages.

      Is this possible? If so, can anyone help me figure out how to do this, or where I can find a good tutorial?

      Thanks.
        • 1. Re: PHP & MySQL recordset question
          Ben M Adobe Community Professional
          What error message are you receiving?
          • 2. Re: PHP & MySQL recordset question
            9thReg Level 1
            I get an error message saying that the SQL syntax is not correct and I need to check the manual. I also get an error message saying that topic and thread in the WHERE clause is ambiguous. The last error message that I get is about the ORDER clause, which says the same thing as the WHERE clause.
            • 3. Re: PHP & MySQL recordset question
              Level 7
              Can you give us the EXPLICIT wording of the error messages? That way we are
              not working from a paraphrase.

              --
              Murray --- ICQ 71997575
              Adobe Community Expert
              (If you *MUST* email me, don't LAUGH when you do so!)
              ==================
              http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
              http://www.dwfaq.com - DW FAQs, Tutorials & Resources
              ==================


              "9thReg" <webforumsuser@macromedia.com> wrote in message
              news:g74iia$nnf$1@forums.macromedia.com...
              >I get an error message saying that the SQL syntax is not correct and I need
              >to
              > check the manual. I also get an error message saying that topic and
              > thread in
              > the WHERE clause is ambiguous. The last error message that I get is about
              > the
              > ORDER clause, which says the same thing as the WHERE clause.
              >

              • 4. Re: PHP &amp; MySQL recordset question
                9thReg Level 1
                Thanks for your reply.

                Sorry, here is the entire SQL statement:

                SELECT eventforum.FirstName, eventforum.LastName, eventforum.topic, eventforum.Thread, eventforum.`Date`
                FROM eventforum
                WHERE Thread or topic LIKE %colname%
                UNION
                SELECT fundforum.FirstName, fundforum.LastName, fundforum.topic, fundforum.Thread, fundforum.`Date`
                FROM fundforum
                WHERE Thread OR topic LIKE %colname%
                UNION
                SELECT generalforum.FirstName, generalforum.LastName, generalforum.topic, generalforum.Thread, generalforum.`Date`
                FROM generalforum
                WHERE Thread OR topic LIKE %colname%
                UNION
                SELECT persupport.FirstName, persupport.LastName, persupport.topic, persupport.Thread, persupport.`Date`
                FROM persupport
                WHERE Thread OR topic LIKE %colname%
                UNION
                SELECT prosupport.FirstName, prosupport.LastName, prosupport.topic, prosupport.Thread, prosupport.`Date`
                FROM prosupport
                WHERE Thread OR topic LIKE %colname%
                UNION
                SELECT soldier.FirstName, soldier.LastName, soldier.topic, soldier.Thread, soldier.`Date`
                FROM soldier
                WHERE Thread OR topic LIKE %colname%
                ORDER BY 'Date' DESC

                the variable colname is defined as:

                Name: colname
                Type: Text
                Default Value: -1
                Runtime value: $_POST['searchbox']

                here is the error message that I get with this setup:

                Missing type for variable:colname

                If I remove the variable and the WHERE statements, I get this error message:

                MySQL Error #: 1271
                Illegal mix of collations for operation 'UNION'

                If I remove the variable, the WHERE staements, and the ORDER BY statement, I get the following error message:

                MySQL Error #: 1271
                Illegal mix of collations for operation 'UNION'
                • 5. Re: PHP &amp; MySQL recordset question
                  Level 7
                  .oO(9thReg)

                  >I get an error message saying that the SQL syntax is not correct and I need to
                  >check the manual.

                  Please post the _exact_ and _complete_ error message. It usually
                  contains the position where the error occured.

                  But given your query from the first posting, you're missing some commas
                  and have blanks which shouldn't be there. Please fix those obvious
                  syntax and naming errors first.

                  >I also get an error message saying that topic and thread in
                  >the WHERE clause is ambiguous. The last error message that I get is about the
                  >ORDER clause, which says the same thing as the WHERE clause.

                  If you combine multiple tables which contain fields of the same name,
                  you have to explicitly say which one of them you want to use in the
                  WHERE and ORDER clauses by prepending them with the table name.

                  But the main question is why there are five tables with the same
                  structure instead of just a single one.

                  Micha
                  • 6. Re: PHP &amp; MySQL recordset question
                    Level 7
                    .oO(9thReg)

                    > Sorry, here is the entire SQL statement:
                    >
                    > SELECT eventforum.FirstName, eventforum.LastName, eventforum.topic,
                    >eventforum.Thread, eventforum.`Date`
                    > FROM eventforum
                    > WHERE Thread or topic LIKE %colname%
                    > UNION
                    >[...]

                    You should _really_ fix your database design first! Assuming this is
                    kind of a discussion board, all you need are two tables to start with:
                    One to hold the forum information like title and description, another
                    one for all the threads and postings. Instead of using a separate table
                    for each forum simply put all threads into a single table and let each
                    posting refer to the forum it belongs to (FOREIGN KEY).

                    Micha
                    • 7. Re: PHP &amp; MySQL recordset question
                      bregent Most Valuable Participant
                      Change this:
                      WHERE thread OR topic LIKE %variable%
                      to this:
                      WHERE thread LIKE %variable% OR topic LIKE %variable%

                      Question for you: why does you database contains tables with such similar data?
                      • 8. Re: PHP &amp; MySQL recordset question
                        bregent Most Valuable Participant
                        >If you combine multiple tables which contain fields of the same name,
                        >you have to explicitly say which one of them you want to use in the
                        >WHERE and ORDER clauses by prepending them with the table name.

                        Micha, that is certainly true for like name columns within the select, but is it also true for like name columns within different select statements in a union query? I didn't think it was.

                        >But the main question is why there are five tables with the same
                        >structure instead of just a single one.

                        Very good question.
                        • 9. Re: PHP &amp; MySQL recordset question
                          Level 7
                          .oO(bregent)

                          >>If you combine multiple tables which contain fields of the same name,
                          > >you have to explicitly say which one of them you want to use in the
                          > >WHERE and ORDER clauses by prepending them with the table name.
                          >
                          > Micha, that is certainly true for like name columns within the select, but is
                          >it also true for like name columns within different select statements in a
                          >union query? I didn't think it was.

                          Good question, can't answer that right now. But the OP got that error,
                          even though he didn't post the complete error message and the query that
                          caused it, so it's unclear where exactly it happened.

                          Micha