Array manipulation

[huntsman2]

I have been looking for hours on how to do this and haven't found a solution yet, hope someone here can help! Part of my problem is that I'm new to php and web dev, so I don't know what I'm looking for terminology wise.

Goal: This is part of a form where users can record countries they have visited. If they have already visited the country, I update with the most recent date that they were there; if it was a new visit, we add that entry to the database as a new country visit.

Problem: When I wrote this page, I hardcoded an array with variables that I could reference later in my code if the variable existed. The reason it may not be present is because I am dumping a bunch of user selections from a previous page, so the variable $china would exist if a user had checked that box, otherwise it wouldn't. I am getting these values through $_POST by looping through all the values. I know this probably wasn't right, but it got the job done to test a couple of countries.

What I have now:

//Post values from form
foreach (array_keys($_POST) as $key) {
    $$key = ($_POST[$key]);
}

//my array of possible countries to visit (date is singular as calendar entry for a year, so same for all entries)
$myarray=array($america,$japan,$china)

//check for record for insert or update
foreach ($myarray as $key) {
if (empty($key)) {
    continue;
}
$qry = "select * from countries_visited where country='$key'";
$results = mysql_query($qry);

//If record, update.  Otherwise, insert new
if (mysql_num_rows($results) > 0) {     mysql_query("UPDATE countries_visited set date='$date', timestamp=NOW() where user_id ='{$_SESSION['SESS_MEMBER_ID']} and country ='$key'");
} else {
    mysql_query("INSERT INTO countries_visited (date, user_id, country) VALUES ('$date', '{$_SESSION['SESS_MEMBER_ID']}','$key')");
}
}

In a nutshell, I want the $myarray to be populated the list from my basic countries table:

$query="select concat('$',country) as country from countries";
$result=mysql_query($query);


while($row = mysql_fetch_array($result))
    {
$array[] = $row;
}  

But this isn't working for me. From what I can tell, this is returning a multidimensional array and I'm not handling it correctly. I just want it to store in a basic array like I hardcoded! I concatednated the '$' to make it appear as a variable, as that is how I had it before. Any help is much appreciated

[Atli]

Hey.

That first code is written in a rather bad way. Extracting the contents of an array into variables is not a good idea. There was initially a feature in PHP that always did this for REQUEST variables, called register_globasl, but it's since been removed because, well, it wasn't really a good idea to begin with. (See the link for details.)

Because of that feature, a lot of code since then has been written to mimic it's effects, usually using the extract() function (which does the same thing your loop does). But this is just as bad an idea as the original feature was.

What you want to be doing is dealing with values as values; using the arrays rather than extracting their values into variables and using those.

Based on your description, one way to achieve what you are after would be to do this:

<?php
// Query a list of the countries
$sql = "SELECT country FROM countries";
$result = mysql_query($sql);
if (!$result) {
    // The query failed. ALWASY CHECK to see if query exections
    // fail before using the result!
    trigger_error(mysql_error(), E_USER_ERROR);
}

// Go through the country list
while ($row = mysql_fetch_assoc($result)) {
    // Check if the country was checked in the form.
    if (isset($_POST[$row['country']])) {
        // Build the INSERT/UPDATE query. (I'll explain this
        // later, but essentially this is either an INSERT or an
        // UPDATE query depending on whether the row already exists.)
        $sql = "INSERT INTO `countries_visited` (`date`, `user_id`, `country`)
                VALUES (NOW(), %d, '%s')
                ON DUPLICATE KEY UPDATE `date` = NOW()";
        $sql = sprintf($sql, (int)$_SESSION['SESS_MEMBER_ID'], $row['country']);

        // Execute the query and CHECK THE RESULTS!
        $result = mysql_query($sql);
        if (!$result || mysql_affected_rows() == 0) {
            trigger_error("Failed to insert or update {$country}!");
        }
    }
}

There I just query the country names from the database, and use those values to see if the were checked in the form, using the isset() function. If they were, I execute a INSERT ... ON DUPLICATE KEY UPDATE query to either insert or update the country.

This INSERT variation is built just like these. It eliminates the need to exeucte a SELECT to see if the record already exist, and leaves you having to do just one query instead of two. - It does depend on there being a unique (or primary) key that would be violated if the same user+country combo is entered twice though. (Not hard to set up.)

[huntsman2]

Thanks for the background on your solution, I was definitely following some older tutorials and modifying them for my use.

When I run this it seems to work except that it is only updating/inserting the first country. So if the user selects China, USA, Canada on the form- this code is only writing China. Thoughts?

[Atli]

Are you 100% sure the names of the input boxes in the form match the country names in the database? They have to match exactly, case and all.

If that's not the issue, try to var_dump($_POST) outside the loop, and then var_dump($row) inside the loop just to see what data PHP is working with exactly.

Of course the best thing to do is to set up a debugger, like XDebug (works great with Netbeans), that you can use to set breakpoints. But that's probably overkill to debug a single script. Worth looking into for larger projects though.

[huntsman2]

The values match up exactly because I am populating them in the form with the data from the same table, so they match case for case.

The two var_dumps return all results and don't have any values missing. It just seems like the loop is cutting off after the first entry.

[Atli]

Ahh yes, of course. On line #24 of my code I overwrite the $result variable with the result of the second query. Change that variable name to something else (as well as the one in the following line) and it should run through the rest of the countries.

Sorry, my mistake.

[huntsman2]

Perfect! Problem solved, thanks for the help and solution