0

I'm trying to build a CMS for a website that I'm building and I need to be able to edit the article's that were saved to the database. The problem I'm facing is I have a lot of query statements 1 that's selecting all the articles fields values for my inputs and then another one that's selecting everything from my categories table (For a Select Drop Down with all the categories I have for options), what would be the best way to get all the records from categories and only the record that has the art_id that's = to the one I'm passing through URL.

TABLE CATEGORIES

cat_id  cat_name
1       soccer
2       baseball
3       basketball

TABLE ARTICLES

art_id art_cat_id art_title
1      1          lorem

HTML / PHP

<?php
 $sql_articles = "SELECT art_id, art_cat_id, art_title, cat_id, cat_name
                FROM app_articles LEFT JOIN app_categories
                ON app_articles.art_cat_id = app_categories.cat_id
                WHERE art_id =".$_GET['art_id']; 

$result = query($sql_articles);
if($result===false) {
    echo("query failed");
}
else {
    $row = mysqli_fetch_array($result)              
?>

<form id="articles" action="" method="post">

<input type="text" name="title" value="<?php echo $row['art_title']; ?>">

<select name="category">
    <option value="<?php echo $row['art_cat_id'] ?>" selected="selected">
            <?php echo $row['cat_name'] ?>
    </option>
    <?php
    $sql_categories = "SELECT cat_id, cat_name FROM app_categories";

    $result = query($sql_categories);

    if($result===false) {
            echo("Query Fail");
    }
    else {
        while( $data = mysqli_fetch_array($result)) {
            if ($row['art_cat_id'] == $data['cat_id']) continue;
    ?>     
            <option value="<?php echo $data['cat_id'] ?>">
                    <?php echo $data['cat_name'] ?>
             </option>
    <?php
        }
    }
?>
</select>

<input type="submit" value="Save">
</form>
<?php
    }
?>

I Guess what I'm asking is that is it possible to do a sql statment that will return something like

art_id  art_cat_id  art_title  art_slug  art_company cat_id  cat_name
1       1           lorem      lorem     lorem       1       soccer
                                                     2       baseball
                                                     3       basketball

So I can do this is 1 statement, thanks in advance for anyhelp!

1 Answer 1

1

Yes you can use GROUP_CONCAT for MySQL query that will give you combined result for category name.

Good Example

SQL DEMO

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.