Rebuild SQL database, creating new topic tables using post ID

A few years ago, I exported an SQL database for a forum without an opportunity to double-check the integrity of the file and after importing the database into a site backup, I found that all tables after “P” are missing. Posts are intact but the topic tables are gone. I copy/pasted the missing tables from a fresh installation just to get the database to load and found that a select few posts display in test threads, allowing me to view some content but obviously without the correct thread title.

I’d like to put together an SQL query that will automatically create new topicIDs using the available posts so that I can rebuild the topics/posts for viewing. I could do this manually by keying everything into the SQL file myself but that would take a tremendous amount of time given the database contains between 3-4k posts.

I’m not SQL savvy, only knowing the basic functions of phpMyAdmin so whatever relevant information needed will need to be requested. I’ll add a few things here that may be of some use.

I initially sought support via the Microsoft SQL forums and was redirected here but only after being given an example query.

INSERT INTO dbo.Thread(postID, content)
SELECT Post.postID, NULL
FROM dbo.Post
WHERE NOT EXISTS(
    SELECT 1
    FROM dbo.Thread
    WHERE Thread.postID = Post.postID
    AND Post.postDate < '20140101' --date of bad export
);

MySQL tables are in UTF-8. Not sure if it’d be of any help to list available table keys or not but I can provide those when asked.

Forum software is Invision Power Board. Tech support referred me to my web host and this is far outside my host’s scope of support. This is really my last ditch effort to save the remaining post data for archival purposes.


Source: stackoverflow-php

PHP foreach INSERT into database with Datatables

I am trying to insert a line into a database for every row that has text within the text input in that row, I don’t have much experience using for-each loops so I’m probably doing something simple wrong…

This is my mark-up:

<form id="" action="test-price-quote.php" method="POST">
<table id="csp" class="table table-striped table-bordered display">
    <thead>
        <tr>
            <th>Product Code</th>
            <th>Product Name</th>
            <th>Band A Price</th>
            <th>Matrix</th>
            <th>New Price</th>
        </tr>
    </thead>
    <tbody>
        <?php while($res = sqlsrv_fetch_array($def, SQLSRV_FETCH_ASSOC)) : ?>
            <tr>                                
                <td><?php echo $res['ItemCode']; ?></td>
                    <input type="hidden" name="ItemCode[]" value="<?php echo $res['ItemCode']; ?>" />
                <td><?php echo $res['ItemName']; ?></td>
                    <input type="hidden" name="ItemName[]" value="<?php echo $res['ItemName']; ?>" />
                <td><?php echo $res['CurrentBandA']; ?></td>
                    <input type="hidden" name="CurrentBandA[]" value="<?php echo $res['CurrentBandA']; ?>" />
                <td><?php echo $res['Matrix']; ?></td>
                    <input type="hidden" name="Matrix[]" value="<?php echo $res['Matrix']; ?>" />
                <td><input type="text" name="csp-price[]" value=""></input></td>
            </tr>
        <?php endwhile; ?> 
    </tbody>
</table>
</form>

This is my PHP and Insert script:

<?php

    if (isset($_POST['submit'])) { 

        foreach ($_POST['csp-price'] as $price) { 
            $quoteID = $row['QuoteID'];

            $query = "INSERT INTO po_special_price_products (QuoteID, CardCode, SlpCode, SlpName, BandA, NewPrice) VALUES (?, ?, ?, ?, ?, ?)"; 
            $stmt = sqlsrv_prepare($sapconn2, $query); 

            if (sqlsrv_execute($stmt) === false) { 
                die(print_r(sqlsrv_errors(), true)); 
            } 
        } 
    }
?>

What I am expecting to happen is any row that has text inputted will insert that row of data into the database. I am also using jQuerys datatables, could this be affecting it not inserting?

I am currently getting “COUNT field incorrect or syntax error” from research my understanding that this is when I am not inserting the same amount of columns that are in the database, but I am, so maybe I have misunderstood this.

Any help in the right direction would be great, thanks in advance.


Source: stackoverflow-php

Create Table from Another Table – MYSQL to MSSQL conversion

I’m in the process of converting a database from MYSQL to MSSQL/TSQL, and I can’t get the following MYSQL query to work in MSSQL. Looking for a fresh set of eyes to see what I’m missing.

Working MYSQL Query:

CREATE TABLE tempTable 
AS (SELECT * FROM Note WHERE createdate IN (SELECT MAX(createdate) 
FROM Note GROUP BY `taskPK`));

The query looks for the max date of a note row for a given id. There may be multiple Note rows with the same ID and it will find the newest row and insert it into the new table.

Non-Working MSSQL Example 1: (Incorrect Syntax near ‘(‘)

CREATE TABLE tempTable 
AS(SELECT * FROM dbo.Note WHERE createdate IN (SELECT MAX(createdate) 
FROM dbo.Note GROUP BY taskPK));

Non-Working MSSQL Example 2: (Incorrect Syntax near the keyword INTO)

SELECT * 
FROM Note 
WHERE createdate IN (SELECT MAX(createdate) FROM Note GROUP BY taskPK) 
INTO tempTable FROM Note;

This new table (once created) will be used in the following query (which I haven’t tested yet in MSSQL):

SELECT Task.id, Task.taskdescription, Task.status, TaskSchedule.date, TaskGroups.name, tempTable.createDate FROM Task 
LEFT JOIN TaskSchedule ON Task.id = TaskSchedule.taskPK
LEFT JOIN TaskGroups ON Task.taskgroupid = TaskGroups.id
LEFT JOIN tempTable ON Task.id = tempTable.taskPK
WHERE Task.userPK = '$userPK' AND Task.status = 'Open'
ORDER BY tempTable.createDate DESC;

The above query selects a task that includes the newest note from the previous query I’m having problems with — adding this incase I’m making this more difficult than it needs to be.


Source: stackoverflow-php

Error: mssql_connect(): Unable to connect to server:

What could be the problem that some of the cron functions which always connects to the database using the code below suddenly started to fail?

It seems that it can’t connect to DB. Credentials are good, they didn’t changed. TestConnect is a class which is called then I want to connect to my DB. I use it like this :

$test = new TestConnect(true);

Now the class file is found and it is working as far as I understand.

class TestConnect
{

private $_server = 'test';
private $_username = 'test';
private $_password = 'password';
private $_database = 'database';
private $_query = null;
private $_handle = null;
private $_handle_db = null;
private $_result = null;

public function __construct($connect = null, $query = null)
{
    if ($connect)
    {
        $this->connect();
    }

    if ($query)
    {
        $this->query($query);
    }
}

public function connect()
{
    ini_set('memory_limit','1024M');
    putenv("FREETDSCONF=/etc/freetds.conf");

    if ($this->_handle)
    {
        return $this->_handle;
    }
    else
    {
        if ( ! $this->_handle = mssql_connect($this->_server, $this->_username, $this->_password))
        {
            throw new Exception($this->get_error_message());
        }

        if ( ! $this->_handle_db = mssql_select_db($this->_database, $this->_handle))
        {
            throw new Exception($this->get_error_message());
        }
    }
}
}


Source: stackoverflow-php