Jump to content

Multi inserts in to my database.


jmac2501

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/87513-multi-inserts-in-to-my-database/
Share on other sites

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());

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

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.

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

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);

?>

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.

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

Archived

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

×
×
  • 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.