clicking one button affects all values in database

[berserk]

I wasn’t sure which forum to put this under so I just selected web development. I have run into an issue that ironically I have solved before but thanks to my wonderful drop-box the changes I made were not saved and when I changed them again on another computer, the old values were kept so in a sense I lost my progress/solution. Now onto my problem.

I have a table that generates buttons to give you the option to approve or decline a user. Now the approve button calls a function that will create a database dynamically by checking to see if it already exists and if it doesn’t then it will create a new one with a new name. This is not really my problem but I felt a little explaining might clear that part of the code up. My issue is that the other function was to insert data into this newly created table and then delete the old data from the old temporary data table. This part is tricky because it will work but when I click the button to approve it will approve all of them, and I can't seem to remember how I managed to separate them before. I will include the code below to show you what I mean but any and all help is as always greatly appreciated, thanks in advance.

this is my loop to create th ebuttons dynamically

while($row=mysqli_fetch_array($query)){
                  // add one to rownumber each time
                  // get the id from each row so you can access all the data values in each column
                  $id=$row['id'];
                  ?>
                        <tr>
                          <!-- each one of these access values that are stored under a specified header in the database -->
                         <td><?php echo @$row['email'] ?></td>
                         <td><?php echo @$row['company_name'] ?></td>
                         <td><?php echo @$row['username'] ?></td>
                         <td><?php echo @$row['date'] ?></td>
                         <td><?php echo @$row['license'] ?></td>
                         <td><?php echo @$row['state'] ?></td>
                         <td><?php echo @$row['phone'] ?></td>
                         <td><input onclick="javascript:approve_account_function(<?php echo $row['id'];?>, <?php echo $rownumber;?>)" class="btn btn-success" name="yes_account<?php echo $rownumber;?>" id="yes_account<?php echo $rownumber;?>" value="Approve"></input></td>
                         <td><input onclick="javascript:decline_account_function(<?php echo $row['id'];?>, <?php echo $rownumber;?>)" class="btn btn-danger" name="no_account<?php echo $rownumber;?>" id="no_account<?php echo $rownumber;?>" value="Decline"></input></td>
                         <input type="hidden" name="sub_id<?php echo $rownumber;?>" id="sub_id<?php echo $rownumber;?>" value="<?php echo $row['id']; ?>"/>
                         <input type="hidden" name="user_name<?php echo $rownumber;?>" id="user_name<?php echo $rownumber;?>" value="<?php echo $row['username']; ?>"/>
                      </tr>
                        <?php 
                        $rownumber++;
                      } ?>

and this is the function i call to insert the data after i have created the table if one desont exist yet.

$total = $_POST['row_total'];
$table_link = "subscriber_table";

$table_sql = mysqli_query($con, "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='myles'");
$table_count = mysqli_fetch_row($table_sql);

// approve the selected subscriber
// function approve_subscriber($sub_id_num, $table_connection){

// }

// check to see what tables are already there so we dont create a table that has the same name already.
for ($i=0; $i <= $table_count[0]-7; $i++) { 
    $table = $table_link.$i;
    if(mysqli_num_rows(mysqli_query($con, "SHOW TABLES LIKE '".$table."'"))==1){
        //echo "Table exists";
        // do nothing
        echo "it sees a table";
        echo "<br>";
    }
    else {
        // table does not exist, so create one
        $sql = "CREATE TABLE $table (
        id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
        consumer_state VARCHAR(50) NOT NULL,
        consumer_vehicle_type VARCHAR(50) NOT NULL,
        account_number VARCHAR(50) NOT NULL,
        select_reason_for_default VARCHAR(50) NOT NULL,
        upload_gps_track_history VARCHAR(50) NOT NULL,
        consumer_name VARCHAR(100) NOT NULL,
        consumer_street_address VARCHAR(100) NOT NULL,
        consumer_city VARCHAR(100) NOT NULL,
        consumer_zip VARCHAR(100) NOT NULL,
        is_there_a_cosign VARCHAR(100) NOT NULL,
        cosigner_name VARCHAR(100) NOT NULL,
        cosigner_street_address VARCHAR(100) NOT NULL,
        cosigner_city VARCHAR(100) NOT NULL,
        cosigner_state VARCHAR(100) NOT NULL,
        cosigner_zip VARCHAR(100) NOT NULL,
        consumer_vin VARCHAR(100) NOT NULL,
        date_contract_signed VARCHAR(100) NOT NULL,
        name_of_location_collateral_sold VARCHAR(100) NOT NULL,
        address_of_location_collateral_sold VARCHAR(100) NOT NULL,
        time_of_sale_or_auction VARCHAR(100) NOT NULL,
        date_of_repo_sale_or_auction VARCHAR(100) NOT NULL,
        repossession_date VARCHAR(100) NOT NULL,
        reg_date TIMESTAMP
        )";
        if (mysqli_query($con, $sql) === TRUE) {
            echo "Table ".$table." created successfully";
        } else {
            echo "Error creating table: " . $con->error;
        }
    }
}

// for ($i=0; $i <= $total-1; $i++) {
//  if (isset($_POST['user_name'])) {
//      $id = $_POST['sub_id'.$i];
//      $add_sql = "INSERT INTO approved_users SELECT d.*, '".$table."' FROM temp_users d WHERE id='".$id."'";
//      $delete_sql = "DELETE FROM temp_users WHERE id='".$id."'";

//      if (mysqli_query($con, $add_sql)) {
//          mysqli_query($con, $delete_sql);
//          echo "Successfully Approved User";
//          // table modified and updated
//      }
//  }
// }

for ($i=0; $i <= $total-1; $i++) {
    if (isset($_POST['yes_account'.$i])) {
        $id = $_POST['sub_id'.$i];
        $add_sql = "INSERT INTO approved_users SELECT d.*, '".$table."' FROM temp_users d WHERE id='".$id."'";
        $delete_sql = "DELETE FROM temp_users WHERE id='".$id."'";

        if (mysqli_query($con, $add_sql)) {
            mysqli_query($con, $delete_sql);
            echo "Successfully Approved User";
            // table modified and updated
        }
    }
}

ive been at this for days and even though i know there is a simple way to do it i just cant see it.

[ryantroop]

May just be a preference thing.. but making tables on the fly like that is probably not a good idea (and resource heavy), and then you iterate through your table list with each request...

Just by reading your code, trying to figure out what you intend to accomplish is difficult...

That said.. without knowing what your scipt is sending (or the var_dump($_POST)) it's impossible to figure out why you are getting the multiple updates. Since you are iterating through all permutations each time you send a request, I would assume your data is not correct coming to your code, and it is working quite admirably and doing exactly what you told it to.

also, for inline onclick handlers, you don't need "javascript:" anymore. Most browsers know it's javascript (of course, Im assuming you're writing HTML/4+, so YMMV).

[berserk]

thanks for the pointers, i apologize for some of the errors in my code but what i was trying to accomplish was to have a user create a table and then once that table is created (within the same script) have a function take data from a temp table and copy it over to the newly created one, i wasnt sure if this was possible all in one script, and i know the table creation is resource heavy but its neccessary for the system im making. When a user registers their information is stored in a temporary table until an admin can approve the info, when this approval is done what i have described above will happen, the table will be generated for that subscriber and the data stored in the temp table will be pushed to their newly created table.

[diafol]

WHy do your users need their own table anyhow? If the structure is identical for everybody?

[berserk]

mainly i was doing this for organization, because i can create a new table just for that client when they register a user it will be added to that table that is associated with their account/table, i know this is a bit bulky but to me this makes sense, is there a better way to do this because i know this is probably not the norm but its a way i have found will work for my needs.

Thanks

[diafol]

That is one way. But there may be an issue if you have to make changes to your table. You then have to make changes to every single table you created this way. Unlless you have a tables of table names, then you'd probably have to do so manually. An alternative would be to just have a user table and have the user_id as a foreign key in those tables where users 'own' the data.

[berserk]

thank you diafol, its funny because my solution was exactly as you say, i used a table of tables to be able to name the tables properly and reference them properly too. thanks for all the help, its been a blast figuring this out and getting ready for the end of this project!