3

I made a mysql query that will copy 3 columns of data from one table to another:

INSERT INTO eio3k_hikers (link_id, link_name, user_id) SELECT link_id, link_name, user_id FROM eio3k_mt_links where eio3k_mt_links.link_id = (3)

I made a button which when clicked it should execute the query. My code looks like this:

<html>
<form action='' method='POST'>
    <input type='submit' name='submit' />
</form>
</html>

<?php 
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
    include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
    define('JPATH_BASE', dirname(__FILE__));
    require_once JPATH_BASE.'/includes/defines.php';
}
require_once JPATH_BASE.'/includes/framework.php';

// Get a db connection.
$db = JFactory::getDbo();

if(isset($_POST['submit'])){
    // My query 
    $querySelect = $db->getQuery(true);
    $querySelect->select('link_id, link_name, user_id');
    $querySelect->from($db->quoteName('#__mt_links'));
    $querySelect->where($db->quoteName('#__mt_links.link_id')." = ".$db->quote(3));

    $queryInsert = $db->getQuery(true);
    $queryInsert->insert($db->quoteName('#__hikers'));
    $queryInsert->columns($db->quoteName(array('link_id, link_name, user_id')));
    $queryInsert->values($querySelect);
    $db->setQuery($queryInsert);
}

When I click on the button, the data does not insert. I also tried this in the last line:

$db->setQuery($queryInsert)->execute();

But then I got error:

1136 Column count doesn't match value count at row 1

although they are the same columns in both tables.

Improve this question
0

3 Answers 3

Reset to default
0

I think @Lodder is right. You've got to set the first query and then also execute it. After the line...

$db->setQuery($querySelect); ... also add a ...

$db->execute($querySelect);

... then continue on as Lodder suggested. :-)

Improve this answer
1

There are a couple of problems with your select and where clause

  • If you want to select multiple columns, you need to define the columns as an array.
  • You don't need to insert the table name in the where clause.
  • In the select clause, you don't appear to be defining the results to then insert

Try this updated code:

$querySelect = $db->getQuery(true);
$querySelect->select($db->qn(array('link_id', 'link_name', 'user_id')));
$querySelect->from($db->qn('#__mt_links'));
$querySelect->where($db->qn('link_id') . ' = ' . $db->q(3));
$db->setQuery($querySelect);

$results = $db->loadRowList();

$results is now the variable that contains your results. It may contain more than 1 row of results so you will then need to use a foreach loop when inserting the data.

Have a look through the documentation on how to perform database queries: https://docs.joomla.org/Special:MyLanguage/Inserting,_Updating_and_Removing_data_using_JDatabase

Improve this answer
0
0

You ARE NOT going crazy. MySQL and Joomla DO allow you to INSERT rows from a SELECT query in a single transaction. I have tested the following to be successful on my localhost, but if you don't believe me, see what Nagarjun has to say. Making two separate database calls to SELECT then INSERT is missing out on a beautiful feature of MySQL.

Here is your lone syntax hiccup:

$queryInsert->columns($db->quoteName(array('link_id, link_name, user_id')));

This says you want to push the 3 columns of data into a single, 27-character column called:

`link_id, link_name, user_id`

Why is that? You merely missed the inner single quotes and produced a 1-element array. This would have been an easy thing to overlook, since you probably copy-pasted from your SELECT clause. It should be written like this:

$queryInsert->columns($db->quoteName(array('link_id', 'link_name', 'user_id')));

I'll suggest some other modifications infused with my personal preferences:

  • You can chain your query methods from the getQuery() method and spare retyping $queryWord on each line.
  • Your select() method doesn't need to receive an array of columns (of course, it can). In your case, a comma separated string containing the three column names is perfectly valid/correct.
  • qn() (quoteName() as you are using it) is not actually necessary for any of the tables or columns that you have mentioned because they are whole words and not "reserved words".
  • When you want to secure a dynamic integer value in your query, you can just write (int) before it to cast it as an integer. If you are using static/hardcoded numbers, you can write them without quotes. If you are using static/hardcoded strings, you can manually wrap them in single quotes.
  • In your project, if you are using any user-supplied variables/data as values in your WHERE clause, then you should call q() (or quote()) on your variables for security reasons.
  • When your code is live/public, never display your rendered query string or the exact error message.
  • A try {} catch {} block is a clean way to handle errors that may occur while querying.

My suggested code:

$db = JFactory::getDBO();
    try {
        $querySelect = $db->getQuery(true)
                          ->select('link_id, link_name, user_id')
                          ->from('#__mt_links')
                          ->where('link_id = 3');

        $queryInsert = $db->getQuery(true)
                          ->insert('#__hikers')
                          ->columns(array('link_id', 'link_name', 'user_id'))
                          ->values($querySelect);

        echo $queryInsert->dump();
        $db->setQuery($queryInsert);
        $db->execute();
        echo "<div>Affected Rows: {$db->getAffectedRows()}</div>";
    } catch (Exception $e) {
        echo "<div>Error: {$e->getMessage()}</div>";
    }

Rendered query (from ->dump()):

INSERT INTO eio3k_hikers
(link_id,link_name,user_id)
(
SELECT link_id, link_name, user_id
FROM eio3k_mt_links
WHERE link_id = 3)
Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.