Multi inserts in to my database.

jmac2501 · January 24, 2008

Hey-

I can't figure out why my script inserts the same info over and over again. I need it to stop after 1 time throw the field. Is my $i++ in the wrong place????

<?php
error_reporting(E_ALL ^ E_NOTICE);

$link = mysql_connect('mysql6.******.com', '*****', '*****')
    or die('Could not connect: ' . mysql_error());
mysql_select_db('dcremax') or die('Could not select database');
$query="SELECT * FROM Listings";
$result=mysql_query($query);

$num=mysql_numrows($result);

$i=0;
while ($i < $num) {

$reo=mysql_result($result,$i,"reo");

require_once("class_http.php");

$h = new http();

$h->dir = "/home/foo/bar/"; 

if (!$h->fetch("http://metrolistmls.com/cgi-bin/GetOneR.cfm?County=SA&iRow=0&nJL=7&MLSNum=".$reo)) {

  echo "<h2>There is a problem with the http request!</h2>";
  echo $h->log;
  exit();
}

if($beg = strpos($h->body, "http://mlsmedia.metrolistmls.com/bigphoto/", 0 ))
{$end = strpos($h->body, ".jpg", $beg );
$pic = substr($h->body, $beg, ($end + 4) - $beg );
echo("$pic");
echo("<br />");
}
{
$link = mysql_connect('mysql6.******.com', '******', '******')
    or die('Could not connect: ' . mysql_error());
mysql_select_db('dcremax') or die('Could not select database');   
$sql = "INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`)
VALUES ('$streetaddress','$city','$price','$squarefeet','$bed','$Fbath','$Hbath','$REO','$agent','$pic')";
echo("$sql");
mysql_query($sql);
if(mysql_query($sql))
{
  echo "Success!";
}

else{
  echo("                       FAILED TO INPUT DATA <p><a href=\"../Members/Error.htm\">CLICK HERE</a></p>");
}
}
$i++;
}
mysql_close($link);
?>

PHP Monkeh · January 24, 2008

<?php $num=mysql_numrows($result); ?>

That should be

<?php $num=mysql_num_rows($result); ?>

As it is now, it'll just be returning 0 I believe, so that's why you'll only be getting 1 inserted.

jmac2501 · January 24, 2008

ok ci changed that and its still doing the same thing. What is strange is that the echo shows it only once but when i check my database, the same info is uploaded multipal times.... :-\

Any other ideas?

revraz · January 24, 2008

What is this { for, should there be a else there?

Also, I dont know how your code even runs with those syntax errors.

echo("$pic");

echo("<br />");

}

{ <---------------

$link = mysql_connect('mysql6.******.com', '******', '******')

or die('Could not connect: ' . mysql_error());

jmac2501 · January 24, 2008

well it runs. but not right. how would you fix it?

jmac2501 · January 24, 2008

??? i can't figure out why it inserts multiple inputs... anyone else have any idea?

PHP Monkeh · January 24, 2008

Hold on, you don't want it to insert multiple times? O.o I thought that was what you wanted :|

jmac2501 · January 24, 2008

ok say i have 15 colums in my reo field that i am calling. I want it to run the 15 colums, re-insert them into my database. but i keep getting any where from 4-10 enteries each.

Does that make sense?

jmac2501 · January 24, 2008

So no i don't want them to repeat. here are some screen shots:

Here is what gets echoed:

http://mlsmedia.metrolistmls.com/bigphoto/090/70127190.jpg
154,900
4800 Westlake Pkwy Unit 1002
t 1002, Sacramento, 
1304
2
2
0
Diane Cox
INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`) VALUES ('4800 Westlake Pkwy Unit 1002','t 1002, Sacramento, ','154,900','1304','2','2','0','','Diane Cox','http://mlsmedia.metrolistmls.com/bigphoto/090/70127190.jpg')Success!
INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`) VALUES ('4800 Westlake Pkwy Unit 1002','t 1002, Sacramento, ','154,900','1304','2','2','0','','Diane Cox','http://mlsmedia.metrolistmls.com/bigphoto/090/70127190.jpg')Success!http://mlsmedia.metrolistmls.com/bigphoto/003/70107903.jpg
375,000
5221 White Lotus Way
Elk Grove, 
2650
5
3
0
Diane Cox
INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`) VALUES ('5221 White Lotus Way',' Elk Grove, ','375,000','2650','5','3','0','','Diane Cox','http://mlsmedia.metrolistmls.com/bigphoto/003/70107903.jpg')Success!
INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`) VALUES ('5221 White Lotus Way',' Elk Grove, ','375,000','2650','5','3','0','','Diane Cox','http://mlsmedia.metrolistmls.com/bigphoto/003/70107903.jpg')Success!

and here is what the database looks like now.

2767               70127190     
        2769               70107903              
      2776 4800 Westlake Pkwy Unit 1002 t 1002,
         Sacramento,  154,900 1304 2 2 0   Diane Cox http://mlsmedia.metrolistmls.com/bigphoto/090/7012... 
      2793 4960 Yvonne Way      Sacramento,  189,900 1663 3 2 1   Diane Cox http://mlsmedia.metrolistmls.com/bigphoto/070/7009... 
      2792 4960 Yvonne Way      Sacramento,  189,900 1663 3 2 1   Diane Cox http://mlsmedia.metrolistmls.com/bigphoto/070/7009... 
      2791 4960 Yvonne Way      Sacramento,  189,900 1663 3 2 1   Diane Cox http://mlsmedia.metrolistmls.com/bigphoto/070/7009... 
      2790 4960 Yvonne Way      Sacramento,  189,900 1663 3 2 1   Diane Cox http://mlsmedia.metrolistmls.com/bigphoto/070/7009... 
      2789 2921 Wright St     Sacramento,  171,000 880 2 1 0   Diane Cox http://mlsmedia.metrolistmls.com/bigphoto/029/7012... 
      2788 2921 Wright St     Sacramento,  171,000 880 2 1 0   Diane Cox http://mlsmedia.metrolistmls.com/bigphoto/029/7012...

PHP Monkeh · January 24, 2008

I don't actually understand what it is you're trying to do still (sorry!). But I still have some questions about your code.

Why do you have 2 $link ?

And where are you getting all the values from which you're inserting:

$sql = "INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`)
VALUES ('$streetaddress','$city','$price','$squarefeet','$bed','$Fbath','$Hbath','$REO','$agent','$pic')";

I can't see where all those variables are coming from, the only one I can see is $reo.

jmac2501 · January 24, 2008

all the other values are genarated in the WHOLE code . i didn;t want to post the whole code but if you want i will.

I made some changes but sill getting the echo repeated.

<?php
error_reporting(E_ALL ^ E_NOTICE);

$link = mysql_connect('mysql6.brinkster.com', 'dcremax', 'results1')
    or die('Could not connect: ' . mysql_error());
mysql_select_db('dcremax') or die('Could not select database');
$query="SELECT * FROM Listings";
$result=mysql_query($query);

$num=mysql_num_rows($result);

$i=0;
while ($i < $num) {

$reo=mysql_result($result,$i,"reo");


require_once("class_http.php");

$h = new http();

$h->dir = "/home/foo/bar/"; 

if (!$h->fetch("http://metrolistmls.com/cgi-bin/GetOneR.cfm?County=SA&iRow=0&nJL=7&MLSNum=".$reo)) {

  echo "<h2>There is a problem with the http request!</h2>";
  echo $h->log;
  exit();
}

if($beg = strpos($h->body, "http://mlsmedia.metrolistmls.com/bigphoto/", 0 ))
{$end = strpos($h->body, ".jpg", $beg );
$pic = substr($h->body, $beg, ($end + 4) - $beg );
echo("$pic");
echo("<br />");
}
if($beg = strpos($h->body, "<td align=\"center\" class=\"PageTitle\" width=\"534\"><b> $", 0 ))
{$end = strpos($h->body, "</b></td>", $beg );
$price = substr($h->body, $beg + 55, ($end - 55) - $beg );
echo("$price");
echo("<br />");
}

if($beg = strpos($h->body, "<td class=\"PageTitle\" colspan=\"3\" align=\"center\"><b>", 0 ))
{$end = strpos($h->body, ",", $beg );
$streetaddress = substr($h->body, $beg + 52, ($end - 52) - $beg );
echo("$streetaddress");
echo("<br />");

$str = '$streetaddress    ';
}

if($beg = strpos($h->body, "<td class=\"PageTitle\" colspan=\"3\" align=\"center\"><b>", 0 ))
{$end = strpos($h->body, "</b></td>", $beg );
$city = substr($h->body, $beg + 56 + strlen($str), ($end - 61 - strlen($str)) - $beg );
echo("$city");
echo("<br />");
}

if($beg = strpos($h->body, "<td width=\"16%\" class=\"FormTitle\">Sq Ft</td>
              <td width=\"14%\">", 0 ))
{$end = strpos($h->body, "</td>
              <td width=\"16%\"", $beg );
$squarefeet = substr($h->body, $beg + 76, ($end - 76) - $beg );
echo("$squarefeet");
echo("<br />");
}

if($beg = strpos($h->body, "<td width=\"16%\" class=\"FormTitle\">Bedrooms</td>", 0 ))
{$end = strpos($h->body, "</td>
              <td width=\"16%", $beg );
$bed = substr($h->body, $beg + 97, ($end - 97) - $beg );
echo("$bed");
echo("<br />");
}

if($beg = strpos($h->body, "<td width=\"16%\" class=\"FormTitle\">Full Baths</td>", 0 ))
{$end = strpos($h->body, "</td>
              <td width=\"16%", $beg );
$Fbath = substr($h->body, $beg + 81, ($end - 81) - $beg );
echo("$Fbath");
echo("<br />");
}

if($beg = strpos($h->body, "<td width=\"16%\" class=\"FormTitle\">Half Baths</td>", 0 ))
{$end = strpos($h->body, "</td>
              <td width=\"16%", $beg );
$Hbath = substr($h->body, $beg + 99, ($end - 99) - $beg );
echo("$Hbath");
echo("<br />");
}

if($beg = strpos($h->body, "<td width=\"60\"><b>Agent</b></td>", 0 ))
{$end = strpos($h->body, " ", $beg );
$agent = substr($h->body, $beg + 85, ($end - 85) - $beg );
if(trim($agent) == "Diane C Cox")$agent = "Diane Cox";
if(trim($agent) == "Lizette Gonzalez 
              </td>
            </tr>
          </table>
        </td>
      </tr>
      <tr>
        <td height=\"17\" colspan=\"3\">
          <table cellspacing=\"0\" cellpadding=\"2\" border=\"0\">
            <tr>
              <td width=\"60\"><b>Office</b></td>
              <td class=\"body14\">
                <a href=\"../Offices/01rmxg08.cfm\" class=\"link2\"><b>RE/MAX Gold Sacramento</b></a>")$agent = "Lizette Gonzalez";
echo("$agent");
echo("<br />");
}
if(trim($pic) == "")$pic = "../images/nophoto1b_small.jpg";
if(isset($price))
{
$sql = "INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`)
VALUES ('$streetaddress','$city','$price','$squarefeet','$bed','$Fbath','$Hbath','$reo','$agent','$pic')";
echo("$sql");
mysql_query($sql);
if(mysql_query($sql))
{
  echo "Success!";
}

else{
  echo("                       FAILED TO INPUT DATA <p><a href=\"../Members/Error.htm\">CLICK HERE</a></p>");
}
}
$i++;
}
mysql_close($link);
?>

and here is the echo output.

http://mlsmedia.metrolistmls.com/bigphoto/093/70025593.jpg
109,900
4900 44th St
Sacramento, 
860
2
1
0
Diane Cox
INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`) VALUES ('4900 44th St',' Sacramento, ','109,900','860','2','1','0','70025593','Diane Cox','http://mlsmedia.metrolistmls.com/bigphoto/093/70025593.jpg')Success!http://mlsmedia.metrolistmls.com/bigphoto/061/70084761.jpg
99,900
3708 46th Ave
Sacramento, 
824
2
1
0
Diane Cox
INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`) VALUES ('3708 46th Ave',' Sacramento, ',' 99,900','824','2','1','0','70084761','Diane Cox','http://mlsmedia.metrolistmls.com/bigphoto/061/70084761.jpg')Success!INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`) VALUES ('3708 46th Ave',' Sacramento, ',' 99,900','824','2','1','0','70087892','Diane Cox','http://mlsmedia.metrolistmls.com/bigphoto/061/70084761.jpg')Success!INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`) VALUES ('3708 46th Ave',' Sacramento, ',' 99,900','824','2','1','0','70090748','Diane Cox','http://mlsmedia.metrolistmls.com/bigphoto/061/70084761.jpg')Success!INSERT INTO listings(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`) VALUES ('3708 46th Ave',' Sacramento, ',' 99,900','824','2','1','0','70054751','Diane Cox','http://mlsmedia.metrolistmls.com/bigphoto/061/70084761.jpg')Success!

See the insert is looping...

PHP Monkeh · January 24, 2008

I'll make a proper reply after this but first remove your database info from your code.

jmac2501 · January 24, 2008

whoops... can't edit it eather. ran out of time.

PHP Monkeh · January 24, 2008

The reason your insert is looping is because all of your code from the while() to the last } is include in the loop.

<?php

while ($i < $num) { // YOUR LOOP STARTS HERE

// STUFF THATS LOOPING

} // YOUR LOOP ENDS HERE ON LINE 130
mysql_close($link);

?>

jmac2501 · January 24, 2008

well then how should i do it?

This is what i need it to do.

1) get the first field from my database -------> 111

2) place it into the http:// string ----> if (!$h->fetch("http://metrolistmls.com/...=7&MLSNum=".111))

3) scrap the website for all the other values

4) upload the values to the database

5) start over with the second field -------> 222

ect.

jmac2501 · January 25, 2008

So no more Ideas? :'(

rab · January 26, 2008

<?php
error_reporting(E_ALL ^ E_NOTICE);

// Put this up here
require_once("class_http.php");

// MySQL Connection
$link = mysql_connect('mysql6.******.com', '*****', '*****') || die('Could not connect: ' . mysql_error());
mysql_select_db('dcremax') || die('Could not select database');

// IMO, i only put the query in a variable if it's not going to be a constant
$result = mysql_query("SELECT * FROM Listings") || die(mysql_error());
$num = mysql_numrows($result);

for($i=0; $i<$num; $i++) {
$reo = mysql_result($result,$i,"reo");
$h = new http();
$h->dir = "/home/foo/bar/"; 

if( !$h->fetch("http://metrolistmls.com/cgi-bin/GetOneR.cfm?County=SA&iRow=0&nJL=7&MLSNum=".$reo) ) {
	echo "<h2>There is a problem with the http request!</h2>";
	echo $h->log;
	exit();
}

// Grab all the info
if( preg_match_all("@http://mlsmedia\.metrolistmls\.com/bigphoto/(\d+)/(.*?)\.(jpg|png|jpeg|gif)@i", $h->body, $pictures) )
	$pic = $pictures[0];

if( preg_match('@<td class="PageTitle" colspan="3" align="center"><b>(.*?)</b></td>@i', $h->body, $street) ) 
	$street = $street[1];

if( preg_match('@<td align="center" class="PageTitle" width="534"><b> (\$\s*.*?)</b></td>@i', $h->body, $price) ) 
	$price = $price[1];

// ... more and more ...

if(trim($pic) == "")
	$pic = "../images/nophoto1b_small.jpg";

if( !empty(trim($price) ) {
	$sql = sprintf("INSERT INTO listings ".
					"(`streetaddress`, `city`, `price`, `squarefeet`, `bed`, `Fbath`, `Hbath`, `reo`, `agent`, `pic`) VALUES ".
					"('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
					mysql_real_escape_string($street),
					mysql_real_escape_string($city),
					mysql_real_escape_string($price),
					mysql_real_escape_string($squarefeet),
					mysql_real_escape_string($bed),
					mysql_real_escape_string($fbath),
					mysql_real_escape_string($hbath),
					mysql_real_escape_string($reo),
					mysql_real_escape_string($agent),
					mysql_real_escape_string($pic));
	mysql_query($sql) || die(mysql_error());
}
}

mysql_close($link);
?>

Finish the information extracting.

Sign In

Multi inserts in to my database.

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information