INSERT INTO not working one table but works on others & update works too?

Question

My PHP/SQL script does not work on the new table I cloned but works perfectly fine on the table I copied the new table from using this:

SELECT * INTO slide FROM news

Here's my insert form:

<div class="the-form" style="width:100%;">
<form class="userTrans" method="post">
<input type="hidden" name="act_userTrans" value="__insertNews_">
    <p>
        <label for="title">Title:</label>
        <input type="text" name="title" id="title">
    </p>
    <p>
        <label for="type">News Type:</label>
        <input type="text" name="type" id="type">
    </p>
    <p>
        <label for="autor">Author:</label>
        <input type="text" name="autor" id="autor">
    </p>

    <input type="text" name="text2" id="text2">
    <p class="form-footer">
        <button class="button userTrans" style="background-color: #DB6D1D;">Publish News</button>
    </p>
</form>
</div>

And here's my edit form:

<div class="the-form" style="width:100%;">
<form class="userTrans" method="post">
<input type="hidden" name="act_userTrans" value="__updateNews_">
    <p>
        <label for="title">Title:</label>
        <input type="text" name="title" id="title" value="<?=$edit[title]?>"/>
    </p>
    <p>
        <label for="type">News Type:</label>
        <input type="text" name="type" id="type" value="<?=$edit[type]?>"/>
    </p>
    <p>
        <label for="autor">Author:</label>
        <input type="text" name="autor" id="autor" value="<?=$edit[autor]?>"/>
    </p>

        <input type="text" name="text2" id="text2" value="">
        <input type="hidden" name="id" value="<?=$edit[id]?>">

    
    <p class="form-footer">
        <button class="button userTrans" style="background-color: #DB6D1D;">Publish News</button>
    </p>
</form>
</div>

Here's my process for updating & inserting my two different forms:

    if($activity == "__insertNews_")
    {
        $title = htmlspecialchars($_POST['title']);
        $autor = htmlspecialchars($_POST['autor']);
        $type = (int)$_POST['type'];
        if(empty($_POST['type']) || empty($_POST['autor']) || empty($_POST['title']) || empty($_POST['text2']))
        {
            echo response(0,'Fill up all the forms.',0);
            exit();
        }
        //echo $_POST[text2];
        $news = mssql_query("INSERT INTO DB1.dbo.news (title,text,type,autor) VALUES ('$title','$_POST[text2]','$type','$autor') ");
        echo response(1,'Publishing '.$title.' success!',0);
    }
    
    if($activity == "__updateNews_")
    {
        $title = htmlspecialchars($_POST['title']);
        $autor = htmlspecialchars($_POST['autor']);
        if(empty($_POST['autor']) || empty($_POST['title']))
        {
            echo response(0,'Fill up all the forms.',0);
            exit();
        }
        $news = mssql_query("UPDATE DB1.dbo.news set title='$title',autor='$autor' WHERE id='$id'  ");
        echo response(1,'Editing '.$title.' success!',0);
    }

So using those scripts above and i was able to INSERT & UPDATE any contents on dbo.news

However, when I change DB1.dbo.news to the new table I created ( DB1.dbo.slide ) the "INSERT" won't work.

I tried to add data using the same form & processing script, the "INSERT" won't work on dbo.slide but when I test it on dbo.news I'm able to insert data. I also tested UPDATE, and it's working on both dbo.slide and dbo.news.

Now I'm wondering, why is it that the SAME script for INSERT is working on other table but it does not work on the new one (dbo.slide). It's literally confusing because I did not change any codes, I just changed the table I'm inserting the data into and the INSERT function stopped working.

What's the best way to debug this and find out what is causing this issue?

Two notes: 1) You are using old and unsupported PHP extension (`mssql_` functions). 2) To get information about the possible errors check the result from the `mssql_query()` call and get possible error information with `mssql_get_last_message()`. — Zhorov, Jul 27 '21 at 05:50
`INSERT * INTO table FROM...` is not valid, it should be `INSERT INTO table SELECT * FROM...` or `INSERT INTO table(columns) SELECT * FROM...` — Charlieface, Jul 27 '21 at 05:58
`$_POST[text2]` is strange. It should be `'$_POST[text2']` in the `INSERT` statement. And can you execute `mssql_get_last_message()` after the `mssql_query()` call and post the output? Thanks. — Zhorov, Jul 27 '21 at 08:36
Hello @Zhorov thank you for the time! I have to work on this old function because it is the currently and only available/compatible script for the game im working/playing around. — Amenadiel, Jul 27 '21 at 08:47
@Zhorov I tried using mssql_get_last_message() and i get this message when i use INSERT query on dbo.slider `The statement has been terminated` (the insert query only doesn't work on dbo.slider, it works on dbo.news) I get this message: `Changed database context to 'master'` when i use INSERT query on dbo.news or whenever i use UPDATE query on dbo.slider & dbo.news (UPDATE query works on both tables, INSERT does not work on dbo.slider but works on normally on dbo.news.) — Amenadiel, Jul 27 '21 at 08:48
I also already tried removing the $_POST[text2] from the `INSERT` statement and just used this `INSERT INTO db1.dbo.slider (title) VALUES ('$title')` and still no luck making it work but when i tried the same statement/query on db1.dbo.news, i was able to insert the data. — Amenadiel, Jul 27 '21 at 08:52

score 0 · Answer 1 · answered Jul 27 '21 at 06:12

0

mssql_query function no longer exist in newer php versions. i suggest you to work with PDO because one of it advantage for you is, reduce the commonly used insert and update operations and it's safe against malicious attacks and through SQL injection

But now, can't help you without DB1.dbo.slide columns and DB1.dbo.slide columns and your DB1.dbo.slide insert query.

So for main help, check DB1.dbo.slide columns and compare their with DB1.dbo.news. then rewrite your SQL query according to DB1.dbo.slide columns.

If your insert query worked for DB1.dbo.news, be sure your insert query for DB1.dbo.slide is not correct

answered Jul 27 '21 at 06:12

Daniyal Sedighpour

129
1
8

I have to setting for this old mssql function for now because this is the only compatible script for the game i'm working/playing around. Btw, here are the columns of db1.dbo.slide `type(varchar(100),null) title(varchar(100),null) author(varchar(100),null) p_img(varchar(100),null) p_msg(text, null) date(datetime, not null) text(text,null) pin(int,null)` Those are also the same columns (100% the same) of db1.dbo.news because i just cloned the entire db1.dbo.slide from db1.dbo.news using this query: `SELECT * INTO slide FROM news` – Amenadiel Jul 27 '21 at 08:21
About the insert query i used, i've posted it above already but here it is: `mssql_query("INSERT INTO db1.dbo.slider (title,text,type,autor) VALUES ('$title','$_POST[text2]','$type','$autor') ");` dbo.slide & dbo.news 100% have the same columns because i just cloned everything of dbo.slide from dbo.news The issue is that, despite having the same column and using the same insert query, INSERTING data to the sql server only works on dbo.news because when i tried using the same INSERT query on dbo.slide, nothing happens. But the UPDATE query works on both tables (dbo.slide & dbo.news). – Amenadiel Jul 27 '21 at 08:22

score 0 · Accepted Answer · answered Jul 28 '21 at 04:14

SOLVED! I deleted all the cloned database and used the CREATE TABLE statement to recreate everything including primary keys, constraint, etc and now its working!

It seems that i shouldn't be using this to clone tables

SELECT * INTO slide FROM news

Thank you everyone for taking the time to reply, really appreciate it!

INSERT INTO not working one table but works on others & update works too?

2 Answers2