Jump to content

PHP Mysql query update not working


clarencek

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
        )
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

[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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.