Results 1 to 15 of 15
  1. #1

    Multiple Condition SELECT query (MySQL + PHP)

    How should one select a row with multiple conditions?
    i.e. A table (table) has the columns id, category, feature, and text.
    How could you do SELECT text FROM table WHERE category = 'news' AND feature = 'computers' ?
    Would that be a correct query?

    Here's what I have in my script, and it seems to only take the first conditon:
    PHP Code:
    $mtgrabdba "SELECT template_id FROM mt_template WHERE template_blog_id = '" $getmtbid['0'] . "' AND template_type = 'archive'";
                
    $mtgrabdbares mysql_query($mtgrabdba) or die("Could Not Perform Query Because".mysql_error());
                
    $mtdbaid mysql_fetch_array($mtgrabdbares);

                
    $mtgrabca "SELECT template_id FROM mt_template WHERE template_blog_id = '" $getmtbid['0'] . "' AND template_type = 'category'";
                
    $mtgrabcares mysql_query($mtgrabdba) or die("Could Not Perform Query Because".mysql_error());
                
    $mtcaid mysql_fetch_array($mtgrabcares);
                
                
    $mtgrabiea "SELECT template_id FROM mt_template WHERE tem[plate_blog_id = '" $getmtbid['0'] . "' AND template_type = 'individual'";
                
    $mtgrabieares mysql_query($mtgrabdba) or die("Could Not Perform Query Because".mysql_error());
                
    $mtieaid mysql_fetch_array($mtgrabieares); 
    Although each of those should return a different ID, it ends up with $mtdbaid, $mtcaid, and $mtieaid being the very same array, which is becoming a problem.

    Can anyone help?
    I can post the INSERT queries I use those values in if necessary.

  2. #2
    Join Date
    Apr 2004
    Location
    Port St Lucie, FL
    Posts
    117

    Some data would help..

    I could probably help you figure it out if you'll post what the INSERT query looks like.
    Paul Embry
    Knight Software and Web Design
    [email protected]
    Quality PHP Web Programming for Reasonable Prices

  3. #3
    Join Date
    Apr 2004
    Location
    Port St Lucie, FL
    Posts
    117
    Bleh, double post. Sorry.
    Paul Embry
    Knight Software and Web Design
    [email protected]
    Quality PHP Web Programming for Reasonable Prices

  4. #4
    Here they are...
    PHP Code:
                $mt3tempqueryx mysql_query("INSERT INTO mt_templatemap (templatemap_blog_id, templatemap_template_id, templatemap_archive_type, templatemap_is_preferred) 
                                    VALUES (
                                    '" 
    $getmtbid['0'] . "', 
                                    '" 
    $mtdbaid['0'] . "', 
                                    'Daily', 
                                    '1')"
    );
                if (!
    $mt3tempqueryx) {
                echo 
    mysql_error();
                }
                
    $mt4tempqueryx mysql_query("INSERT INTO mt_templatemap (templatemap_blog_id, templatemap_template_id, templatemap_archive_type, templatemap_is_preferred) 
                                    VALUES (
                                    '" 
    $getmtbid['0'] . "', 
                                    '" 
    $mtdbaid['0'] . "', 
                                    'Weekly', 
                                    '1')"
    );
                if (!
    $mt4tempqueryx) {
                echo 
    mysql_error();
                }
                
    $mt5tempqueryx mysql_query("INSERT INTO mt_templatemap (templatemap_blog_id, templatemap_template_id, templatemap_archive_type, templatemap_is_preferred) 
                                    VALUES (
                                    '" 
    $getmtbid['0'] . "', 
                                    '" 
    $mtdbaid['0'] . "', 
                                    'Monthly', 
                                    '1')"
    );
                if (!
    $mt5tempqueryx) {
                echo 
    mysql_error();
                }
                
    $mt6tempqueryx mysql_query("INSERT INTO mt_templatemap (templatemap_blog_id, templatemap_template_id, templatemap_archive_type, templatemap_is_preferred) 
                                    VALUES (
                                    '" 
    $getmtbid['0'] . "', 
                                    '" 
    $mtcaid['0'] . "', 
                                    'Category', 
                                    '1')"
    );
                if (!
    $mt6tempqueryx) {
                echo 
    mysql_error();
                }
                
    $mt7tempqueryx mysql_query("INSERT INTO mt_templatemap (templatemap_blog_id, templatemap_template_id, templatemap_archive_type, templatemap_is_preferred) 
                                    VALUES (
                                    '" 
    $getmtbid['0'] . "', 
                                    '" 
    $mtieaid['0'] . "', 
                                    'Individual', 
                                    '1')"
    );                                
                if (!
    $mt7tempqueryx) {
                echo 
    mysql_error();
                } 
    Tell me if you have anything!
    Also: $getmtbid['0'] just returns the blog id

  5. #5
    Do you mean $mtdbaid[template_id], $mtcaid[template_id], and $mtieaid[template_id] are always the same value?

    Do you expect a single row returned or multiple rows?

  6. #6
    Join Date
    Apr 2004
    Location
    Port St Lucie, FL
    Posts
    117
    Those inserts are for a totally different table than you're selecting from! (Selecting from mt_template, inserting into mt_templatemap)

    And Intername has a valid question - all three of those arrays will have the same index - 'template_id' - hopefully with different values (i.e, $mtdbaid['template_id'] != $mtcaid['template_id'] )
    Paul Embry
    Knight Software and Web Design
    [email protected]
    Quality PHP Web Programming for Reasonable Prices

  7. #7
    It's not easy to explain what I am trying to do in text.
    When I get home, I will take screenshots of the tables to illustrate better what I am trying to do.

    Let's see what I can do with words now:
    I am making a script so that whenever a user gets their phpBB account activated, it creates a blog for them in MovableType.
    However, there are multiple MySQL queries that need to be done to accomplish this.
    Five main things must be done to the database: A user must be created (mt_author), a blog must be created (mt_blog), the blog must be associated with the user (mt_permission), the blog templates must be placed in SQL (mt_template), then the templates must be associated with the type of page view (mt_templatemap).
    I have a query creating a user working, I have a query creating the blog working, I have a query associating the blog working, and I have a query placing the templates in SQL working.
    I am just having a problem setting the templatemap.

    The mt_templatemap has an auto-incrementing field, and three main data fields (along with one that remains NULL, and one that remains '1').
    These three main fields are templatemap_blog_id, which needs to be the blog_id from the mt_blog table (which I have as $mtgetbid['0']);
    templatemap_template_id, which needs to be the correct template_id from mt_template. This is the part I am having a problem with. There are three different templatemap_template_id values I need to insert into the table different numbers of times;
    The last one is templatemap_archive_type: This needs to be daily, weekly, monthly, category, or individual.

    What the rows should look like for this table (NOTE: MADE UP IDs):
    templatemap_blog_id: 6 templatemap_template_id: 7 templatemap_archive_type: Daily
    templatemap_blog_id: 6 templatemap_template_id: 7 templatemap_archive_type: Weekly
    templatemap_blog_id: 6 templatemap_template_id: 7 templatemap_archive_type: Monthly
    templatemap_blog_id: 6 templatemap_template_id: 8 templatemap_archive_type: Category
    templatemap_blog_id: 6 templatemap_template_id: 9 templatemap_archive_type: Individual


    What the rows do look like from the queries (NOTE: MADE UP IDs):
    templatemap_blog_id: 6 templatemap_template_id: 7 templatemap_archive_type: Daily
    templatemap_blog_id: 6 templatemap_template_id: 7 templatemap_archive_type: Weekly
    templatemap_blog_id: 6 templatemap_template_id: 7 templatemap_archive_type: Monthly
    templatemap_blog_id: 6 templatemap_template_id: 7 templatemap_archive_type: Category
    templatemap_blog_id: 6 templatemap_template_id: 7 templatemap_archive_type: Individual


    As you can see the wrong variables are getting passed into the queries.
    I will post screenshots of the tables when I get home, for now, see if that information helps.

  8. #8
    If you build your queries into variables like $sql = "select blah from blah" and then run your query $result = mysql_query($sql) you can always 'echo/print' your $sql out to see what the query is being passed.

    In fact when I'm debugging I just echo without executing the queries - this will help you see what is happening at run time.

  9. #9
    Originally posted by intername
    If you build your queries into variables like $sql = "select blah from blah" and then run your query $result = mysql_query($sql) you can always 'echo/print' your $sql out to see what the query is being passed.

    In fact when I'm debugging I just echo without executing the queries - this will help you see what is happening at run time.
    beat me to it..

    That and var_dump($result) (or whatever other variable you wanna know the result of) can display everything you need to know about the results of your queries and variables..

    Might also suggest doing like $mt_b_id_res or similar "spacing" to make it easier to spot typo's... but to each their own style..

    still looks like he's got his tables mixed up, though....

  10. #10
    Join Date
    Apr 2004
    Location
    Port St Lucie, FL
    Posts
    117
    I'm waiting to see the dumps of the tables - from the mySql Admin tool I'm assuming.

    That should prove enlightening.
    Paul Embry
    Knight Software and Web Design
    [email protected]
    Quality PHP Web Programming for Reasonable Prices

  11. #11
    Found it!

    ProdigySim you are calling the same select for each one: mysql_query($mtgrabdba). You should be calling mysql_query($mtgrabca) for the second and mysql_query($mtgrabiea)

    At the moment you are calling mysql_query($mtgrabdba) for each one - hence the same result!

    Is that applause I hear...

  12. #12
    Awww, man! This is why you don't do copy-paste jobs, people *feels dumb*

    Thanks a BUNCH, intername!

  13. #13

  14. #14
    Join Date
    Apr 2004
    Location
    Port St Lucie, FL
    Posts
    117
    Well done! I swear I must be blind sometimes..
    Paul Embry
    Knight Software and Web Design
    [email protected]
    Quality PHP Web Programming for Reasonable Prices

  15. #15
    I've spent ages staring at the same piece of code only to find out I had overlooked the obvious - not enough beer!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •