Jump to content

Archived

This topic is now archived and is closed to further replies.

clarencek

PHP Mysql query update not working

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

Share this post


Link to post
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

Share this post


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

Share this post


Link to post
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
        )

Share this post


Link to post
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]

Share this post


Link to post
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]

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
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?

Share this post


Link to post
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

Share this post


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

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


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

Share this post


Link to post
Share on other sites

×

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.