My code is Parsing HTML tags using regex and store all the links as an array inside the data base
I have a problem in my code i don't know how to fix it to save the links inside MySQL
i see this error message Error: SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$domain = "https://google.com";
$input = @file_get_contents($domain) or die("Could not access file: $domain");
$regexp = "<a\s[^>]*href=(\"??)([^\" >]*?)\\1[^>]*>(.*)<\/a>";
if(preg_match_all("/$regexp/siU", $input, $matches, PREG_SET_ORDER)) {
foreach($matches as $match) {
$url=$match[2];
// $match[2] = link address
// $match[3] = link text
}
}
$rows = array($domain, $url);
$stmt = $conn->prepare("INSERT INTO linkss(id, domain, url)
VALUES (NULL, :domain, :url)");
foreach($rows as $key => $value){
$stmt->bindParam($key, $value);
}
$stmt -> execute();
echo "New records created successfully";
}
catch(PDOException $e){
echo "Error: " . $e->getMessage();
}
$conn = null;
The way you are passing the data to the prepared statement is incorrect, you using the index to the array - which is a 0 based numerically indexed array. This is why you get the error. Although not sure why you need this array...
$rows = array($domain, $url);
Instead I would suggest using...
$stmt = $conn->prepare("INSERT INTO linkss(id, domain, url)
VALUES (NULL, :domain, :url)");
foreach($url as $value){
$stmt->bindParam(':domain', $domain);
$stmt->bindParam(':url', $value);
$stmt -> execute();
}
This should also insert a record for each URL rather than the last one as the execute() is inside the loop.
Update:
You also need to amend the code which builds the list of URL's, this was previously overwriting the last URL all the time, this will create a list of all of the URLs...
$url = array();
foreach($matches as $match) {
$url[]=$match[2];
}
When binding the parameter you need to specify the : in the parameter name:
$stmt->bindParam(':' . $search_field, $search_val);
You are getting an error because this is missing and the code falls back to expecting an integer value to indicate the parameter position (as if you were using ?-style parameters).
Note this description of the first parameter for PDOStatement::bindParam() from the documentation.
Parameters
Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.