Table does not exist?

General PHP Help
#1

I’m trying to run a script I found in an amazing blog about sports. The instructions were

“Open the php file in a text editor, enter your MySQL connection parameters, and the desired span of years for which to get data. Uncomment the lines that create the table structure.”

I keep getting “#! /usr/bin/php Could not execute query: Table ‘nba.covers’ doesn’t exist”

Can someone please tell me what to ‘uncomment’?!? Thank you.

#! /usr/bin/php

<?php //$file="Covers.sql"; $host="localhost"; $user="root"; $pass="pass"; //exec("mysql -u ".$user." -p".$pass." -h ".$host." < ".$file); $con=mysql_connect($host,$user,$pass) or die ("Could not connect: " . mysql_error()); $htmldoc = new DOMDocument(); $url = file_get_contents('http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/nba/teams/teams.html'); @$htmldoc->loadHTML($url); $doc = $htmldoc->documentElement; $teams=array(); $table=$doc->getElementsbyTagName("td"); foreach ($table as $t) { if ($t->getAttribute("class") != "datacell") continue; foreach ($table as $t) { $ahref=$t->getElementsbyTagName("a"); foreach ($ahref as $a) { $teams[$a->textContent]['Link']=substr($a->getAttribute("href"),-15); } } } unset($teams['Login']); unset($teams['Register']); unset($teams['Help']); $startyr = 1990; $endyr = 2011; $pattern = '/[^-\.0-9]*/'; for ($i = $startyr; $i<=$endyr; $i++) { $yr_url = (string)$i."-".(string)($i+1); foreach ($teams as $team=>$value) { $full_url = "http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/nba/teams/pastresults/".$yr_url."/".$teams[$team]['Link']; $contents = file_get_contents($full_url); $htmldoc = new DOMDocument(); @$htmldoc->loadHTML($contents); $doc = $htmldoc->documentElement; $trs = $doc->getElementsbyTagName('tr'); foreach ($trs as $tr) { if ($tr->getAttribute('class')!="datarow") continue; $tds=$tr->getElementsbyTagName('td'); /*$teams[$team]['Date']=mysql_real_escape_string(trim(str_replace("/","",$tds->item(0)->textContent))); $teams[$team]['Home']=(strpos($tds->item(1)->textContent,"@")==0) ? 1 : 0); $teams[$team]['Vs']=mysql_real_escape_string(trim(str_replace("@","",$tds->item(1)->textContent))); $teams[$team]['Score']=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(2)->textContent))); $teams[$team]['Type']=mysql_real_escape_string(trim($tds->item(3)->textContent)); $teams[$team]['Line']=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(4)->textContent))); $teams[$team]['OU']=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(5)->textContent))); */ $date=mysql_real_escape_string(trim(str_replace("/","",$tds->item(0)->textContent))); $home=(strpos($tds->item(1)->textContent,"@")==0) ? 1 : 0; $vs=mysql_real_escape_string(trim(str_replace("@","",$tds->item(1)->textContent))); $line=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(4)->textContent))); $score=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(2)->textContent))); $type=mysql_real_escape_string(trim($tds->item(3)->textContent)); $ou=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(5)->textContent))); $f=strpos($score,'-'); if ($home == 1) { $h=$team; $away=$vs; $ubername=$date.substr($team,0,3).substr($vs,0,3); $hmscore=substr($score,0,$f); $awscore=substr($score,-(strlen($score)-$f-1)); } else { $h=$vs; $away=$team; $ubername=$date.substr($vs,0,3).substr($team,0,3); $line*=-1; $awscore=substr($score,0,$f); $hmscore=substr($score,-(strlen($score)-$f-1)); } $query = "INSERT IGNORE INTO NBA.Covers (DATE, GmNAME, HOME, AWAY, SCORE, TYPE, LINE, OU, HmScore, AwScore) VALUES ('$date','$ubername','$h','$away','$score','$type','$line','$ou','$hmscore','$awscore')"; mysql_query($query,$con) or die ("Could not execute query: " . mysql_error()); } //echo json_encode(array($team=>$teams[$team])); } } mysql_close($con); ?>

PS. The code was written by http://sportsobjective.com/wordpress/

#2

It means that you would change

[php]
//$file=“Covers.sql”;
//exec(“mysql -u “.$user.” -p”.$pass." -h “.$host.” < ".$file);
[/php]

to

[php]
$file=“Covers.sql”;
exec(“mysql -u “.$user.” -p”.$pass." -h “.$host.” < ".$file);
[/php]

This will attempt to build the tables via shell command. Make sure Covers.sql is in the same directory.

#3

thanks m@tt. I tried your suggestion and still have the same error

#! /usr/bin/php Could not execute query: Table ‘nba.covers’ doesn’t exist

#4

Most likely the exec() function is failing. Do you have the Covers.sql file?

Perhaps try using a full path.

$file="/full/path/to/Covers.sql"

#5

Covers.sql exists. The code is:

– phpMyAdmin SQL Dump
– version 3.4.10.1deb1
– http://www.phpmyadmin.net

– Host: localhost
– Generation Time: Oct 15, 2012 at 02:55 PM
– Server version: 5.5.24
– PHP Version: 5.3.10-1ubuntu3.4

SET SQL_MODE=“NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00”;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 */;

–
– Database: NBA

–
– Table structure for table Covers

USE NBA;

CREATE TABLE IF NOT EXISTS Covers.bak (
Gm int(11) NOT NULL AUTO_INCREMENT,
DATE varchar(20) NOT NULL,
GmName varchar(20) NOT NULL,
HOME varchar(20) DEFAULT NULL,
AWAY varchar(20) NOT NULL,
SCORE varchar(20) DEFAULT NULL,
TYPE varchar(55) DEFAULT NULL,
LINE double DEFAULT NULL,
OU double DEFAULT NULL,
LINK varchar(20) NOT NULL,
PRIMARY KEY (Gm),
UNIQUE KEY GmName (GmName)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

I tried using the full path and had the same error.

Sorry to keep bothering you! Any other ideas are much appreciated.

#6

The table name in Covers.sql does not match the PHP code.

Change this:

CREATE TABLE IF NOT EXISTS Covers.bak

to this:

CREATE TABLE IF NOT EXISTS Covers

#7

thanks m@tt…same error!

Stupid question but where is nba.covers being created? Should it not just be covers?

#8

It is assuming you have a database created already named “NBA”

#9

it was created in phpmyadmin. Im using XAMPP. I have the PHP and SQL file in the root dir. Not sure how to make the connection from the PHP file to the database nba in phpmyadmin

#10

Almost got it working!

The last error I got is #! /usr/bin/php Could not execute query: Unknown column ‘HmScore’ in ‘field list’

I created a table using the .sql file. I ran the php file again and got the above error.

Any chance you know what its referring to?

#11

Both HmScore and AwScore are missing from you CREATE TABLE statement. I have no idea what these fields should be. It seems you have a bad Covers.sql file.

#12

Thanks for your help m@tt. A co worker is pretty good with PHP and sql. Going to have him take a look at it.

Again, I appreciate your help.

#13

add in your database credentials and let me know if this does it for you! I noticed that matt had forgot to mention to uncomment $file=“Covers.sql”;
Hope this works for you bro!!!
[php]
#! /usr/bin/php

<?php $file="Covers.sql"; $host="localhost"; $user="root"; $pass="pass"; exec("mysql -u ".$user." -p".$pass." -h ".$host." < ".$file); $con=mysql_connect($host,$user,$pass) or die ("Could not connect: " . mysql_error()); $htmldoc = new DOMDocument(); $url = file_get_contents('http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/nba/teams/teams.html'); @$htmldoc->loadHTML($url); $doc = $htmldoc->documentElement; $teams=array(); $table=$doc->getElementsbyTagName("td"); foreach ($table as $t) { if ($t->getAttribute("class") != "datacell") continue; foreach ($table as $t) { $ahref=$t->getElementsbyTagName("a"); foreach ($ahref as $a) { $teams[$a->textContent]['Link']=substr($a->getAttribute("href"),-15); } } } unset($teams['Login']); unset($teams['Register']); unset($teams['Help']); $startyr = 1990; $endyr = 2011; $pattern = '/[^-\.0-9]*/'; for ($i = $startyr; $i<=$endyr; $i++) { $yr_url = (string)$i."-".(string)($i+1); foreach ($teams as $team=>$value) { $full_url = "http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/nba/teams/pastresults/".$yr_url."/".$teams[$team]['Link']; $contents = file_get_contents($full_url); $htmldoc = new DOMDocument(); @$htmldoc->loadHTML($contents); $doc = $htmldoc->documentElement; $trs = $doc->getElementsbyTagName('tr'); foreach ($trs as $tr) { if ($tr->getAttribute('class')!="datarow") continue; $tds=$tr->getElementsbyTagName('td'); /*$teams[$team]['Date']=mysql_real_escape_string(trim(str_replace("/","",$tds->item(0)->textContent))); $teams[$team]['Home']=(strpos($tds->item(1)->textContent,"@")==0) ? 1 : 0); $teams[$team]['Vs']=mysql_real_escape_string(trim(str_replace("@","",$tds->item(1)->textContent))); $teams[$team]['Score']=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(2)->textContent))); $teams[$team]['Type']=mysql_real_escape_string(trim($tds->item(3)->textContent)); $teams[$team]['Line']=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(4)->textContent))); $teams[$team]['OU']=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(5)->textContent))); */ $date=mysql_real_escape_string(trim(str_replace("/","",$tds->item(0)->textContent))); $home=(strpos($tds->item(1)->textContent,"@")==0) ? 1 : 0; $vs=mysql_real_escape_string(trim(str_replace("@","",$tds->item(1)->textContent))); $line=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(4)->textContent))); $score=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(2)->textContent))); $type=mysql_real_escape_string(trim($tds->item(3)->textContent)); $ou=mysql_real_escape_string(trim(preg_replace($pattern,'',$tds->item(5)->textContent))); $f=strpos($score,'-'); if ($home == 1) { $h=$team; $away=$vs; $ubername=$date.substr($team,0,3).substr($vs,0,3); $hmscore=substr($score,0,$f); $awscore=substr($score,-(strlen($score)-$f-1)); } else { $h=$vs; $away=$team; $ubername=$date.substr($vs,0,3).substr($team,0,3); $line*=-1; $awscore=substr($score,0,$f); $hmscore=substr($score,-(strlen($score)-$f-1)); } $query = "INSERT IGNORE INTO NBA.Covers (DATE, GmNAME, HOME, AWAY, SCORE, TYPE, LINE, OU, HmScore, AwScore) VALUES ('$date','$ubername','$h','$away','$score','$type','$line','$ou','$hmscore','$awscore')"; mysql_query($query,$con) or die ("Could not execute query: " . mysql_error()); } //echo json_encode(array($team=>$teams[$team])); } } mysql_close($con); ?>

[/php]

#14

Your table structure simply doesn’t match the code. I could guess and say that HmScore and AwScoreare integers, but they may be floats. I don’t know.

If you wanted to just add the fields as varchars, determine what the field should be, then modify the field type. You could build the table like

CREATE TABLE IF NOT EXISTS `Covers.bak` (
  `Gm` int(11) NOT NULL AUTO_INCREMENT,
  `DATE` varchar(20) NOT NULL,
  `GmName` varchar(20) NOT NULL,
  `HOME` varchar(20) DEFAULT NULL,
  `AWAY` varchar(20) NOT NULL,
  `SCORE` varchar(20) DEFAULT NULL,
  `TYPE` varchar(55) DEFAULT NULL,
  `LINE` double DEFAULT NULL,
  `OU` double DEFAULT NULL,
  `LINK` varchar(20) NOT NULL,
  `HmScore` varchar(16) NOT NULL,
  `AwScore` varchar(16) NOT NULL,
  PRIMARY KEY (`Gm`),
  UNIQUE KEY `GmName` (`GmName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Then, after you figure out what the values of HmScore and AwScore are, you could change the field to int, smallInt, float, etc. Whatever is applicable.

#15

Also, if you are using MySQL you would want to change ENGINE=InnoDB to ENGINE=MyISAM

#16

This is incorrect, M@tt innoDB is perfectly fine :wink: It is actually perferred by a lot of people it has many advantages over myisam you should read up on it a bit

#17

Thanks for all the help guys. It ended up being the HmScore & AwScore needed to be included in the table. I did try that as per m@tt suggestion but it didnt work last night.

Anyways, all is well now! Thanks again for the help.

Sponsor our Newsletter | Privacy Policy | Terms of Service