Another issue with "INSERT INTO"...

[jckb188]

Struggling to get the following code to work. The code works if I just put an individual table in SELECT FROM, but I can't get anything when I try and do INSERT INTO table and then substitute it in. I am a complete noob at this and I know in advance...I suck :>. Thanks in advance for any help.

<form method="post">
<input type="checkbox" name="table[]" value="A" checked="checked"/>A<br/>
<input type="checkbox" name="table[]" value="B"/>B<br/>
<input type="submit" name="Submit" value="Submit">
</form>
<?php
$con = mysql_connect("","","");
if (!$con)
    {
    die('Could not connect: ' . mysql_error());
    }
mysql_select_db("", $con);
//STEP 5
//initialize an array for the categories
$categories=array();
// check whether any categories have been selected"
$categories = implode(",",$_POST['table']);
print_r ($categories);
if(isset($_POST['table']))
{
    foreach($_POST['table'] as $value) 
    {
        $insert="INSERT INTO table (table) VALUES $categories";
        mysql_query($insert);  
    }
    echo $insert;
}

$query = "SELECT () FROM table"
?>

[broj1]

First, do not think of yourself so negatively, mate. No-one is perfect and we all have our weaknesses as far as programming (and everything else) goes.

Your question is a bit unclear. What is the code that works and what is the code that does not work? Your queries above are incorrect as they are. What is the point of the foreach loop when you do not use $value anywhere?

INSERT INTO table (table) VALUES $categories

What is the purpose of this query? Do you have a column named 'table'? What is the table name?

$query = "SELECT () FROM table"

Something is missing in the above insert statement. And a semicolon is missing too.

[jckb188]

Thank you for your response..

In the SELECT FROM statement, I would like to be able to query and display data from multiple tables that were checked in the checkbox form, but I only want to display those that were checked. From what I have found, I cannot show multiple tables in the FROM section, so I must somehow insert the tables that were selected in the checkbox form into a temporary table that can be utilized in FROM section.

I don't know what the purpose of $value is, I saw that in various other codes so that might be where the problem lies?

Also, the only part that seems to be incorrect is the FROM table part...when I insert the name of a table already in the database, it works fine.

[broj1]

How would you like to query the checked tables? Would you like to JOIN them or query each separately? What is the srtucture of tables? Are they the same? Sory for putting questions instad of answering :-)

[jckb188]

Either way would be fine with me, one issue that I continue to have is I would eventually like to be able to have the user(s) create tables of their own, so I don't have a set list of tables. All of the column headings for each table will be exactly the same, but the amount of rows would differ from table to table.

Thanks very much again.

[broj1]

If you want to let users create their own tables you must use input form fields (one for each table or one for many tables separated by some character such as space or colon). Then you have to store all table names in an array which you will use in foreach loop:

• if you use checkboxes, then $_POST[checkbox_name] will already return an array
• if you use an input form field you get each table name as a string and you have to build an array yourself
• if you use one checkbox for table names separated by a space or colon (or other character) then you use explode function to create an array

If you let users choose their table names can be dangerous since they can unknowingly use mysql keywords. You can avoid that adding a prefix to each table. You might also make sure that different users do not use same names for table names.

The loop for inserting would be something like

<?php
// first check if the form was submitted
// and if it was process the queries
if(isset($_POST['Submit'])) {

    // check if any table names were entered
    if(isset($_POST['table']) and $_POST['table'] != '') {

        // security first: escape the string to avoid SQL injections
        $tables_string = mysql_real_scape_string($_POST['table']);

        // prefix for tables
        $prefix = 'usrtbl_';

        // create tables array (table names are separated by spaces in this example)
        $tables_array = explode(' ', $tables_string);

        // proces tables
        // the data for the fields is in the $categories variable
        // but I do not know what it is and where it comes from
        foreach($tables_array as $one_table) {

            // table name with added prefix
            $table_name = $prefix . $one_table;

            $insert="INSERT INTO `$table_name` VALUES '$categories'";
            mysql_query($insert); 
        }

        // after successful insert you can redirect to some other page
        header('location:thankyou_page.php');
    }
?>

<form method="post">
<input type="input" name="table" value="" />Input table names separated by comma<br/>
<input type="submit" name="Submit" value="Submit">
</form>

[jckb188]

Thank you again. I have a couple questions for you if you have time...

Where should the variable $one_table be drawn from? Does it somehow come from the form?

In the insert function, I am hoping that $table_name will become a table such that I can enter it into this statement "SELECT FROM table_name". Does this happen on the previous step?

In the VALUES '$categories', should it read something like this? - VALUES ('{$_POST['columnA']}', '{$_POST['columnB']}', '{$_POST['columnC']}')";

>

[broj1]

Where should the variable $one_table be drawn from? Does it somehow come from the form?

This is only my assumption since I still do not exactly understand what you want to do: in my previous post I gave you three posibilities to get the table names. For the example in that post I chose the last possibility - the user enters table names in the form, separated by space. So you end up with a string of table names and spaces between them. You use explode function to convert this string into an array. The array of table names is then processed in a foreach loop where $one_table represents the current table name when you loop through the array of table names (see http://php.net/manual/en/control-structures.foreach.php). The array can contain one or more table names.

In the insert function, I am hoping that $table_name will become a table such that I can enter it into this statement "SELECT FROM table_name". Does this happen on the previous step?

Really hard to answer since I do not understand how you want to use the table names.

In the VALUES '$categories', should it read something like this? - VALUES ('{$_POST['columnA']}', '{$_POST['columnB']}', '{$_POST['columnC']}')";

I do not know what the role of categories is here. Maybe you describe the whole concept, what you want to achieve, what is the expected input, the table structures etc. Only after realy understanding what you want to do I can give you useful answers.

[jckb188]

Thank you again for your help.

I am making a calendar, and I would like to group events in the calendar. I have input different events into different tables. For each event, I have 3 attributes(column headers), A, B and C. I would like for the user to be able to display whichever groups (tables) they would like, using a checkbox form, so that some, all or none of the groups would display according to what the user had submitted. At some point, I would like the user to be able to create their own table and be able to submit that as well, but if I could get my own tables to work first that would be excellent.

Thank you again for your help I really appreciate your time.

[broj1]

OK, I am getting the picture. Let's make it step by step. Below is the code to display a form with three checkboxes and a submit button. If the form gets submited the script will read the events from selected tables and display those events. You can adapt this code to suit your requirements. Please note that I have changed the names of tables from table to calendar to make things clear and to avoid possible mistakes (I think it is not a good idea to use general terms or reserved words for variable names). See explanations in comments.

<?php
// if form was submitted and if array with calendar names exists and is not empty
if(isset($_POST['calendar']) && !empty($_POST['calendar']))
{
    // connect to the database
    $con = mysql_connect("","","");
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("", $con);

    // process selected tables (calendars)
    // - read events from each calendar
    // - display events for that calendar
    foreach($_POST['calendar'] as $calendar)
    {
        // query to read events from one calendar
        $q_select = "SELECT * FROM $calendar ";
        $result = mysql_query($q_select);

        // display calendar name
        echo "<h3>$calendar</h3>";

        // list of events (i.e from three columns, separated with a <br />
        // but you could display this in a html table)
        while($row = mysql_fetch_row($result))
        {
            echo "<p>{$row[0]}<br />{$row[1]}<br />{$row[2]}</p>";
        }
    }
}
?>

<form method="post" action="#">
<input type="checkbox" name="calendar[]" value="calendar_A" checked="checked"/>Calendar A<br />
<input type="checkbox" name="calendar[]" value="calendar_B"/>Calendar B<br />
<input type="checkbox" name="calendar[]" value="calendar_C"/>Calendar C<br />
<input type="submit" name="submit" value="Submit">
</form>

Now this is the displaying part. Once we get this clear we can move on to inserting and deleting events. I still do not know what is the role of $categories.

One more advice. Use mysqli extension instead of mysql for dealing with database. The former is newer and recommended. See http://www.php.net/manual/en/book.mysqli.php.

[jckb188]

After a week of attempts it is working with your help! I had previously entered events and they absolutely show in a given month when I submit the form on that month. Thank you thank you thank you!

A couple of things -

I didn't realize it was possible to put a variable in the FROM field?, and I did not even think to loop the query all the way down to the while part. I've got a lot to learn is an understatement.

I think there might be a way such that the calendar will remember my selections so that I can move through the calendar months with my selections picked until I pick new selections. I'm going to research and see what I can find but if I get stuck would it be ok to pm you on the subject?

After that I need to figure out how to allow our users to create their own tables...not sure I am ready to tackle that yet.

Thanks again I feel like I owe you a beer at the very least.

[broj1]

No problem, you owe me nothing :-). I have helped for shear pleasure and I always learn something from that, too.

Just a hint: to remember selections store selected calendars in the $_SESSION array. Seehttp://www.w3schools.com/php/php_sessions.asp. If you have troubles, come back.