PHP Mysql query update not working

[clarencek]

Hi,
I have an array of date set up $sql.

Basically,
$sql[0] is a theater name
$sql[1] is an address
$sql[2] is a show
$sql[3] is a date.

then it starts over again with
$sql[4] is the next theater name, etc....

I am trying to update my database called 'theater' which already has the theater names in the t_name column with the following code:

//Set up connection to database
$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");

for($i = 0; $i < count($sql); $i+4){
mysql_query("Update theater SET t_name = $sql[$i][1] WHERE t_name = $sql[i][1]");
mysql_query("Update theater SET t_address = $sql[$i+1][1] WHERE t_name = $sql[i][1]");
mysql_query("Update theater SET t_show = $sql[$i+2][1] WHERE t_name = $sql[i][1]");
mysql_query("Update theater SET t_updated = $sql[$i+3][1] WHERE t_name = $sql[i][1]");
}

For some reason it is not working.  Any reason why?

Thanks,

CK

[kenrbnsn]

Put the following line in your script to dump the contents of the $sql array and post it here.
[code]<?php echo '<pre>' . print_r($sql,true) . '</pre>' ?>[/code]

When we see the actual data, we might be able to help you.

Also, are you getting any error messages?

Ken

[.josh]

not to mention the fact that you don't need to do a seperate query for all of that... all you need to do is

update table set blah1 = 'blah', blah2 = 'blah', blah3 = 'blah' where blah1 = 'blah'

also notice the ' ' around the values.

[clarencek]

Here are the first three "lines".

Array
(
    [0] => Array
        (
            [0] => Ambassador Theatre
            [1] => Ambassador Theatre
        )

    [1] => Array
        (
            [0] => Chicago
            [1] => Chicago
        )

    [2] => Array
        (
            [0] => 219 West 49th Street
            [1] => 219 West 49th Street
        )

    [3] => Array
        (
            [0] => Nov. 14, 1996
            [1] => Nov. 14, 1996
        )

    [4] => Array
        (
            [0] => American Airlines Theatre
            [1] => American Airlines Theatre
        )

    [5] => Array
        (
            [0] => The Pajama Game
            [1] => The Pajama Game
        )

    [6] => Array
        (
            [0] => 227 West 42nd Street
            [1] => 227 West 42nd Street
        )

    [7] => Array
        (
            [0] => Feb. 23, 2006
            [1] => Feb. 23, 2006
        )

    [8] => Array
        (
            [0] => Brooks Atkinson Theatre
            [1] => Brooks Atkinson Theatre
        )

    [9] => Array
        (
            [0] => The Times they are a Changin'
            [1] => The Times they are a Changin'
        )

    [10] => Array
        (
            [0] => 256 West 47th Street
            [1] => 256 West 47th Street
        )

    [11] => Array
        (
            [0] => Oct. 27, 2005
            [1] => Oct. 27, 2005
        )

[clarencek]

Sorry, again, without the bad formatting.

[code]
Array
(
    [0] => Array
        (
            [0] => <td>Ambassador Theatre</td>
            [1] => Ambassador Theatre
        )

    [1] => Array
        (
            [0] => <td>Chicago</td>
            [1] => Chicago
        )

    [2] => Array
        (
            [0] => <td>219 West 49th Street</td>
            [1] => 219 West 49th Street
        )

    [3] => Array
        (
            [0] => <td>Nov. 14, 1996</td>
            [1] => Nov. 14, 1996
        )

    [4] => Array
        (
            [0] => <td>American Airlines Theatre</td>
            [1] => American Airlines Theatre
        )

    [5] => Array
        (
            [0] => <td>The Pajama Game</td>
            [1] => The Pajama Game
        )

    [6] => Array
        (
            [0] => <td>227 West 42nd Street</td>
            [1] => 227 West 42nd Street
        )

    [7] => Array
        (
            [0] => <td>Feb. 23, 2006</td>
            [1] => Feb. 23, 2006
        )

    [8] => Array
        (
            [0] => <td>Brooks Atkinson Theatre</td>
            [1] => Brooks Atkinson Theatre
        )

    [9] => Array
        (
            [0] => <td>The Times they are a Changin'</td>
            [1] => The Times they are a Changin'
        )

    [10] => Array
        (
            [0] => <td>256 West 47th Street</td>
            [1] => 256 West 47th Street
        )

    [11] => Array
        (
            [0] => <td>Oct. 27, 2005</td>
            [1] => Oct. 27, 2005
        )
[/code]

[clarencek]

Also, I don't get any errors reporting.  Nothing at all appears in the browser window, and when I look at the database, nothing has changed.  Here is my latest code:

[code]
for($i = 0; $i < count($sql); $i+4){
mysql_query("Update theater SET t_name = '$sql[$i][1]', t_address = '$sql[$i+1][1]', t_show = '$sql[$i+2][1]', t_updated = '$sql[$i+3][1]' WHERE t_name = $sql[i][1]");
}
[/code]

[kenrbnsn]

How is this array initialized? Why are there two entries in each sub array?

A more logical structure would be:
[code]<?php
$theaters = array('Ambassador Theatre' =>
array('t_show' => 'Chicago',
't_address' => '219 West 49th Street',
't_date'=>'Nov. 14, 1996'),
'American Airlines Theatre' =>
array('t_show' => 'The Pajama Game',
't_address' => '227 West 42nd Street',
't_date'=>'Feb. 23, 2006'),
'Brooks Atkinson Theatre' =>
array('t_show' => "The Times they are a Changin'",
        't_address' => '256 West 47th Street',
't_date'=>'Oct. 27, 2005'));
?>[/code]

Which could then be processed with the following code to generate the queries:
[code]<?php
foreach ($theaters as $theater => $info) {
$qtmp = array();
foreach ($info as $k=>$v)
$qtmp[] = $k . "='" . mysql_real_escape_string($v) . "'";
$q = "update theaters set " . implode(', ',$qtmp) . " where t_name = '" . $theater . "'";
$rs = mysql_query($q) or die("There was a problem with the query: $q<br>" . mysql_error());
}
?>[/code]

Ken

[.josh]

okay based on your array structure, your $sql is created basically by doing something like this, right?

$sql[0] = array('theatername1','address1','show1','date1');
$sql[1] = array('theatername2','address2','show2','date2');
$sql[2] = array('theatername3','address3','show3','date3');
$sql[3] = array('theatername4','address4','show4','date4');

okay then so we can do a query update like this:
[code]
<?php
foreach ($sql as $key => $val) {
  $query = "Update theater SET t_address = '{$val[1]}', t_show = '{$val[2]}', t_updated = '{$val[3]}' WHERE t_name = '{$val[0]}'";
  mysql_query($query);
}
?>
[/code]

edit: kenrbnsn posted while i was making post

[clarencek]

Maybe it is my noobieness that I am not constructing a clean array.  It was set up as such by crawling a html page:

[code]
if (preg_match_all('/<table.class="theater_list">.+?<\/tr>(.+?)<\/table>/si', $page, $links, PREG_SET_ORDER))

$cells = preg_replace('/<a.href.+?">|<\/a>|<\/?i>/i', '', $links[0][1]);

if (preg_match_all('/<td>(.+?)<\/td>/si', $cells, $sql, PREG_SET_ORDER))
for($i = 0; $i < count($sql); $i++){
echo $sql[$i][1];
  }
[/code]

Was there a better way to get the better array structure suggested?

Thanks.

[clarencek]

To Crayon Violent,

No, my array is not so clean unfortunately.

It's the case that

$sql[0][1]= theatername1
$sql[1][1]= show1
$sql[2][1]= address1
$sql[3][1]= date1
$sql[4][1]= theatername2
$sql[5][1]= show2.....and so on.

This is obviously not good, but I was unsure how else to build the array aside from the code I posted at the time stamp 1:44:37 am.

How can I get a clean array structure as you suggest?

And if I do get there, can you explain your code a little bit?  What does the $key => $val mean or do?  I can interpret the rest okay, but this part is a key step which I'm not sure what it's doing.  Thanks.

[.josh]

okay let's say you have an array:

$blah = array('a','b','c');

you can access each element in the array directly like this:

//example
echo $blah[0]; //echo out the letter 'a'

that number 0 is the position in the array. it is called the key.  the key points to the value, which for key 0 is the letter 'a'. 

so in that foreach loop:
[code]
$blah = array('a','b','c');
echo "key : value <br>";
foreach ($blah as $key => $val) {
  echo $key . " : " $val . "<br>";
}
[/code]
this will print out the following:

key : value
0 : a
1 : b
2 : c

you don't need to use $key or $val specifically. it could be any variable. A lot of people use it for conventional reasons, cuz that's what it is doing: foreach element in the array, make a variable for the key and the value.

as far as making your array "cleaner"... you're going to have to explain your code a bit more.  Are you like, grabbing a file from another website and parsing the info out of it with those pregmatch functions, and that's how you got to have your array setup like that?

[Drumminxx]

[quote]$sql[0][1]= theatername1
$sql[1][1]= show1
$sql[2][1]= address1
$sql[3][1]= date1
$sql[4][1]= theatername2
$sql[5][1]= show2.....and so on.
[/quote]

you may find it easier to build your multi-dimensional array as follows

$sql[0][0]= theatername1
$sql[0][1]= show1
$sql[0][2]= address1
$sql[0][3]= date1

$sql[1][0]= theatername2
$sql[1][1]= show2.....and so on.

then your code to read it back
[code]
for ($i=0; $i<count($sql); $i++) {
   echo $sql[$i][0]; //name
   echo $sql[$i][1]; //show
   echo $sql[$i][2]; //address
   echo $sql[$i][3]; //date
}
[/code]

or

[code]
for ($i=0; $i<count($sql); $i++) {
   mysql_query("UPDATE theater SET t_show = '$sql[$i][1]', t_address = '$sql[$i][2]', t_date = '$sql[$i][3]' WHERE t_name = '$sql[$i][0]'");
}
[/code]

I believe thats what your trying to accomplish

[.josh]

(s)he wants to know how to get it to look like that though, which requires some background info and some more code.

[clarencek]

Crayon (great explanations by the way):

Yes, that's exactly what I'm doing.  Here's a copy of the page that I've been practicing on:

http://www.infofornyc.com/maptest/wiki.html

And the code that I used for the first part:

[code]$url = 'http://www.infofornyc.com/maptest/wiki.html';
$page = file_get_contents($url);

if (preg_match_all('/<table.class="wikitable">.+?<\/tr>(.+?)<\/table>/si', $page, $links, PREG_SET_ORDER))

$cells = preg_replace('/<a.href.+?">|<\/a>|<\/?i>/i', '', $links[0][1]);

if (preg_match_all('/<td>(.+?)<\/td>/si', $cells, $sql, PREG_SET_ORDER))
for($i = 0; $i < count($sql); $i++){
echo $sql[$i][1];
  } [/code]

I imagine I'm not using the functions very well or correctly.  If you can explain a better way to do it, that would be greatly appreciated.  Thanks again.

[.josh]

hmm...well i guess i'd probably do it about the same, as far as getting the data, so i can see how you got what you got. okay let's build on that. After that pregmatch code do this:
[code]
<?php
//we are going to make the assumption that there will be 4 pieces
//of info: theater, title, address, movietime, so therefore the
//total count of $sql should be evenly divided by 4...
$count = count($sql) / 4;

//this will be an offset marker to parse this array by 4's
$offset = 0;

//loop through it count/4 times...
for($i = 0; $i < $count; $i++){
//okay here we will do the count plus the immediate offset(0-3)
                //plus the overall offset of the entire count ($offset)
                $theater = $sql[$i+$offset][1];
$title = $sql[$i+$offset+1][1];
$address = $sql[$i+$offset+2][1];
$movietime = $sql[$i+$offset+3][1];
                //make our array!
$list[] = array('theater' => $theater,
                                    'title' => $title,
                                    'address' => $address,
                                    'movietime' => $movietime);
    //increment the overall offset
    $offset +=3;
}
?>
[/code]
this will make the multi-dimensional array $list that you can use as described in my previous posts for your query. Notice the 'theater' => $theater, etc... the 'theater' is simply giving your key a name so you don't have to refer to it as 0,1,2, etc... so anyways, the query would look like this (from earlier post):

[code]
<?php
foreach ($list as $val) {
  $query = "Update theater SET t_address = '{$val['address']}', t_show = '{$val['title']}', t_updated = '{$val['showtime']}' WHERE t_name = '{$val['theater']}'";
  mysql_query($query);
}
?>
[/code]
that query assumes that you are updating the movie title, address and showtime based on the theater. i'm not sure if that's what you are trying to do, if not, then you will have to adjust your query a bit.


edited to add some comments to the code

[clarencek]

Good stuff!  Thanks.  This pretty much gets me where I want.  I still need to tweak my final html page, but that stuff will be easier for me to do myself.

Thanks a ton.