Jump to content


Photo

PHP Mysql query update not working


  • Please log in to reply
15 replies to this topic

#1 clarencek

clarencek
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 04 July 2006 - 05:07 AM

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

#2 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 04 July 2006 - 05:20 AM

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

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

Also, are you getting any error messages?

Ken

#3 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 04 July 2006 - 05:24 AM

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.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#4 clarencek

clarencek
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 04 July 2006 - 05:30 AM

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
        )


#5 clarencek

clarencek
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 04 July 2006 - 05:34 AM

Sorry, again, without the bad formatting.

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
        )


#6 clarencek

clarencek
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 04 July 2006 - 05:43 AM

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:

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]");
}


#7 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 04 July 2006 - 05:55 AM

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

A more logical structure would be:
<?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'));
?>

Which could then be processed with the following code to generate the queries:
<?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());
	}
?>

Ken


#8 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 04 July 2006 - 06:11 AM

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:
<?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);
}
?>

edit: kenrbnsn posted while i was making post
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#9 clarencek

clarencek
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 04 July 2006 - 06:44 AM

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

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]; 
   } 

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

Thanks.

#10 clarencek

clarencek
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 04 July 2006 - 06:54 AM

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.

#11 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 04 July 2006 - 07:12 AM

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:
$blah = array('a','b','c');
echo "key : value <br>";
foreach ($blah as $key => $val) { 
  echo $key . " : " $val . "<br>";
}
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?
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#12 Drumminxx

Drumminxx
  • Members
  • PipPipPip
  • Advanced Member
  • 92 posts
  • LocationUSA

Posted 04 July 2006 - 07:27 AM

$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.


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
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
}

or

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]'");
}

I believe thats what your trying to accomplish


:: gucci.com ::
:: blockbuster.com ::

--------------------

#13 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 04 July 2006 - 07:31 AM

(s)he wants to know how to get it to look like that though, which requires some background info and some more code.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#14 clarencek

clarencek
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 04 July 2006 - 08:11 AM

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.infoforny...ptest/wiki.html

And the code that I used for the first part:

$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]; 
   }

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.


#15 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 06 July 2006 - 07:03 AM

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:
<?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;
} 
?>
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):

<?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);
}
?>
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
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#16 clarencek

clarencek
  • Members
  • PipPip
  • Member
  • 21 posts

Posted 07 July 2006 - 07:58 AM

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.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users