going mad with a php / mysql script

Question

Ok sorry guys for blabbering earlier this is the full code I have

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully";
echo "<p>Query<p>";

// Query Gets Tag IDs where the Categroy and Tag Alias is the same
$TagID = "SELECT at6ln_discuss_tags.id, at6ln_discuss_tags.title
        FROM at6ln_discuss_tags 
        INNER JOIN at6ln_discuss_category ON at6ln_discuss_tags.alias = at6ln_discuss_category.alias";
$resultTagID = $conn->query($TagID);
//Query Gets Posts IDs where the posts Categroy ID and Category ID are the same
$PostID = "SELECT at6ln_discuss_posts.id
        FROM at6ln_discuss_posts 
        INNER JOIN at6ln_discuss_category ON at6ln_discuss_posts.category_id = at6ln_discuss_category.id";
$resultPostID= $conn->query($PostID);
//Query Gets catergory ID where the Categroy and Tag Alias is the same
$CategoryID = "SELECT at6ln_discuss_category.id
        FROM at6ln_discuss_category 
        INNER JOIN at6ln_discuss_tags ON at6ln_discuss_category.alias = at6ln_discuss_tags.alias";
$resultCategoryID= $conn->query($CategoryID);
//Query Gets Posts catergory ID where the Categroy and Tag Alias is the same
$PostsCategoryID = "SELECT at6ln_discuss_posts.category_id, at6ln_discuss_category.alias
        FROM at6ln_discuss_posts, at6ln_discuss_category
        WHERE (at6ln_discuss_posts.category_id = at6ln_discuss_category.id)";
$resultPostsCategoryID= $conn->query($PostsCategoryID);
//Query Gets catergory Alais where the Categroy and Tag Alias is the same
$PostsCategoryAlias = "SELECT at6ln_discuss_category.alias
        FROM at6ln_discuss_category
        INNER JOIN at6ln_discuss_tags ON at6ln_discuss_category.alias = at6ln_discuss_tags.alias";
$resultPostsCategoryAlias= $conn->query($PostsCategoryAlias);
$sql= "INSERT INTO at6ln_discuss_posts_tags (post_id,tag_id) VALUES ($idposts,$TagID)";
if ($resultTagID->num_rows > 0) {
echo "<table>";
echo "<tr>";
echo "<th> Post ID </th>";
echo "<th> Tag ID </th>";
echo "<th> Post Catergory ID </th>";
echo "</tr>";
 // output data of each row
 while($posts = $resultPostID->fetch_assoc() ) {
     // output data of each row
     $category = $resultCategoryID->fetch_assoc();
     $postscatID = $resultPostsCategoryID->fetch_assoc();
     // output data of each row
     $idcategory = $category["id"];
     $idposts = $posts["id"];
     $idpostcat = $postscatID["category_id"];
     if ($idpostcat == 5){
        $TagID=10;
        query($sql);
            echo "<tr>";
            echo "<td>". $idposts ."</td>";
            echo "<td>". $TagID. "</td>";
            echo "<td>". $idpostcat ."</td>";
            echo "</tr>";
     }
     if ($idpostcat == 7){
        $TagID=12;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 13){
        $TagID=17;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 14){
        $TagID=18;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 15){
        $TagID=19;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 16){
        $TagID=20;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 27){
        $TagID=29;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 28){
        $TagID=30;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 30){
        $TagID=32;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 34){
        $TagID=35;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 35){
        $TagID=36;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 37){
        $TagID=38;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 38){
        $TagID=39;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 49){
        $TagID=47;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 50){
        $TagID=48;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 52){
        $TagID=50;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 53){
        $TagID=51;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
     if ($idpostcat == 55){
        $TagID=46;
        query($sql);
        echo "<tr>";
        echo "<td>". $idposts ."</td>";
        echo "<td>". $TagID. "</td>";
        echo "<td>". $idpostcat ."</td>";
        echo "</tr>";
     }
 }
echo "</table>";} else {
 echo "0 results";}

there is most likly a very easy way of doing this but its basically my first script.

I'm not getting any errors thats my prob, it gets to the while creates the table gets to the second if and then doesn nothing

If I remove the $sql query the table works fine so some thing is wrong in my INSERT and I can't figure it out

Edit: error I'm getting is:

Undefined variable: idposts in /Applications/MAMP/htdocs/DEV/Technet-21/scripts/tags-categorys.php on line 50

Fatal error: Call to undefined function query() in /Applications/MAMP/htdocs/DEV/Technet-21/scripts/tags-categorys.php on line 69

What errors are you getting? See http://stackoverflow.com/a/845025/3794472, http://php.net/manual/en/mysqli.error.php — Jeremiah Winsley, Feb 19 '15 at 15:16
Where are you executing that `INSERT` SQL? I don't see that happening anywhere above. — Patrick Q, Feb 19 '15 at 15:17
Try to reduce your question to exactly your problem... It seems like 3/4th is unnecessary... And at the end we don't even know what is the exact problem you are having. Also, your code is incomplete, at least close the while loop? — Salketer, Feb 19 '15 at 15:17
Echoing salketer's comment. You say you have to update one table with the value from another. Can you be specific - what column are you updating and why? It will help if we know what your goal is. — justAnotherUser, Feb 19 '15 at 15:20
Have you looked at the second page I linked? That will tell you the last mysql error. Echo that after your insert query. — Jeremiah Winsley, Feb 19 '15 at 15:37
It looks like you think that the values used in your query will change when the values of the variables that you used to construct your query change. That is not the case. Unless you use [parameter binding](http://php.net/manual/en/mysqli-stmt.bind-param.php). — Patrick Q, Feb 19 '15 at 15:37
They do change if i remove the query($sql) the table fils up correctly with correct data but as soon as i do the insert it stops and doesn't work — Alex lee, Feb 19 '15 at 15:42
`$conn->query()` is a method of the `$conn` object, `query()` is a function. They aren't the same things. — Jeremiah Winsley, Feb 19 '15 at 15:43
ok so the $conn-> got rid of the fatel error but its not updating the table so i must not be correct in thinking that each time the if is run the varible gets updated-- bloody hell now i'm lost as hell haha — Alex lee, Feb 19 '15 at 15:50
Ok patrick thanks for the Parameter binding i think thats what i'm doing wrong. gonna have a go and see if i can figure that out i guess i need to prepare discuss_posts_tags and then bind with the varibles — Alex lee, Feb 19 '15 at 16:02

score 1 · Accepted Answer · edited May 23 '17 at 12:21

I've rewritten the relevant section of your code. You're running into trouble in a couple locations. The first error is because you're interpolating the variables directly into the insert query.

This is a very bad idea in any case, because it leaves you open to sql injection. You should never trust user supplied input. Take a look at How can I prevent SQL injection in PHP? for more information.

It's also causing you problems because you want the future values of the variables, not the current values, which is what's being inserted when you interpolate variables.

The second error is because you're using query(), which is a function call to an undefined function. Since it's actually a method of the $conn object, you need to use $conn->query().

Another problem your code has, is lots of copy/pasting. You can reduce this and get rid of a lot of those if statements by using a switch/case construct - http://php.net/manual/en/control-structures.switch.php

Here's the modified version of your code:

$sql = 'INSERT INTO at6ln_discuss_posts_tags (post_id,tag_id) VALUES (?,?)';
$stmt = $conn->prepare($sql);
if ($resultTagID->num_rows > 0) {
    echo "<table>";
    echo "<tr>";
    echo "<th> Post ID </th>";
    echo "<th> Tag ID </th>";
    echo "<th> Post Catergory ID </th>";
    echo "</tr>";
    // output data of each row
    while ($posts = $resultPostID->fetch_assoc()) {
        // output data of each row
        $category = $resultCategoryID->fetch_assoc();
        $postscatID = $resultPostsCategoryID->fetch_assoc();
        // output data of each row
        $idcategory = $category["id"];
        $idposts = $posts["id"];
        $idpostcat = $postscatID["category_id"];

        switch($idpostcat) {
            case 5:
                $TagID = 10;
                break;
            case 7:
                $TagID = 12;
                break;
            case 13:
                $TagID = 17;
                break;
            case 14:
                $TagID = 18;
                break;
            case 15:
                $TagID = 19;
                break;
            case 16:
                $TagID = 20;
                break;
            case 27:
                $TagID = 29;
                break;
            case 28:
                $TagID = 30;
                break;
            case 30:
                $TagID = 32;
                break;
            case 34:
                $TagID = 35;
                break;
            case 35:
                $TagID = 36;
                break;
            case 37:
                $TagID = 38;
                break;
            case 38:
                $TagID = 39;
                break;
            case 49:
                $TagID = 47;
                break;
            case 50:
                $TagID = 48;
                break;
            case 52:
                $TagID = 50;
                break;
            case 53:
                $TagID = 51;
                break;
            case 55:
                $TagID = 46;
                break;

        }
        $stmt->bind_param('ii', $idposts, $TagID);
        $stmt->execute();
        echo "<tr>";
        echo "<td>" . $idposts . "</td>";
        echo "<td>" . $TagID . "</td>";
        echo "<td>" . $idpostcat . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

This code uses prepared statements with bound parameters to insert the data safely. You can read more at http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

holly molly, your an absolut boss, i had just given it a go with $sql= "INSERT INTO at6ln_discuss_posts_tags (post_id,tag_id) VALUES (?,?)"; $sql->bind_param(ii,$idposts,$TagID); but got back a fatal error with the bind_param() looking at your code you've used $conn->prepare so i guess that's where i'm going wrong. I truly appriciate your honest help and thanks for showing me the Switch Case i hadn't seen before i knew there was some thing simpler than my if statements but really couldn't figure it out. Thats gonna help me out alot mate thanks — Alex lee, Feb 19 '15 at 16:13
I added a if statement that would make sure that it would only execut the code you wrote if the $idpostcat >=5 as it was picking up things before but that worked like a charm mate thanks very much! — Alex lee, Feb 19 '15 at 16:38

going mad with a php / mysql script

1 Answers1