in_array() works with a fixed array in the file but not from a database

I am going around and around in circles with this and the pdo code is probably wrong now as I am so confused.

Why will the first in_array() work but the second won’t?


Works and displays WAHOO
<?php
$artists = array("Luciano Pavarotti", "Enrico Caruso","Jose Carreras","Placido Domingo");
if ( in_array( 'Enrico Caruso', $artists  ) ) { echo "WAHOO"; }
else{ echo 'BLAH';}
?>

Does not work and displays BLAH
<?php
// Connect to the database
include_once 'PDOsettings.php';
$sth = $PDO->prepare("SELECT full_name FROM performer");
$sth->execute();
$result_performer = $sth->fetchAll();
if ( in_array( 'Enrico Caruso', $result_performer  ) ) { echo "WAHOO"; }
else{ echo 'BLAH';}
?>

The output of print_r() on the database array:

Array ( [0] => Array ( [full_name] => Luciano Pavarotti [0] => Luciano Pavarotti ) [1] => Array ( [full_name] => Enrico Caruso [0] => Enrico Caruso ) [2] => Array ( [full_name] => Jose Carreras [0] => Jose Carreras ) [3] => Array ( [full_name] => Placido Domingo [0] => Placido Domingo ) )

Your running in_array against the wrong element. Try this:


foreach($result_performer as $row) {
  if ( in_array( 'Enrico Caruso', $row  ) ) { echo "WAHOO"; } 
}

Your trying to see if a name exists in an array of arrays. You need to check each array that has the key and values

OK Kyle I did wonder if that could be the problem and I have changed the code to this:


// Connect to the database
include_once 'PDOsettings.php';
$sth = $PDO->prepare("SELECT full_name FROM performer");
$sth->execute();
$result_performer = $sth->fetchAll();
foreach($result_performer as $row) {
if ( in_array( 'Enrico Caruso', $row  ) ) { echo "WAHOO"; } 
else{ echo 'BLAH';}
}

The output is now this:
BLAHWAHOOBLAHBLAHBLAHBLAHBLAHBLAH Which is a bit strangeas it is WAHOOing on a Luciano Pavarotti.

Right, have a look at the logic. foreach $row your doing a check, on fialure to find its 'blah’ing. I’m assuming you only want one output. instead of echoing, set a variable to true, remove the else statement and check if your variable isset()

Though I sense a bigger design flaw is hidden here somewhere. What is the overall task you are trying to perform?

Thanks Kyle; I have had enough tonight as I have been over 5 hours trying to sort this out!

What is the overall task you are trying to perform?

You are correct and this code is validating some form input and the code I had so far was:


if (!empty($_REQUEST['performer_A1'])) {
if ( in_array( $_REQUEST['performer_A1'], $result_per) ) {
$artist_A1 = $_REQUEST['performer_A1']; }
else{ $errors[] = 'There is a problem with artist A1';
}
}
else {$errors[] = 'You forgot to enter an artist A1.';}

The final code should:
Recive the name from the form
Check to make sure there is a name
Check the name is in the database
If the name is not in the database put it there - will need to get the ID for the next part
Link the name to the record

I have a table with performer and performer_ID I also have a table with the record details.
There will be multiple records from the one performer and so to save space I am putting the ID into the record table and not the name.

I hope this has not confused you to much!


--
-- Table structure for table `performer`
--

CREATE TABLE IF NOT EXISTS `performer` (
  `per_ID` smallint(4) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(50) NOT NULL DEFAULT 'Name',
  PRIMARY KEY (`per_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='List of performers' AUTO_INCREMENT=9 ;

--
-- Dumping data for table `performer`
--

INSERT INTO `performer` (`per_ID`, `full_name`) VALUES
(1, 'Luciano Pavarotti '),
(2, 'Enrico Caruso'),
(3, 'Jose Carreras '),
(4, 'Placido Domingo'),
(5, 'Maria Callas'),
(6, 'Joan Sutherland'),
(7, 'Kiri Te Kanawa'),
(8, 'Anna Netrebko');


--
-- Table structure for table `association`
--

CREATE TABLE IF NOT EXISTS `association` (
  `ass_ID` smallint(5) NOT NULL AUTO_INCREMENT COMMENT 'Identifier',
  `number` varchar(50) NOT NULL DEFAULT 'number',
  `company` smallint(4) NOT NULL,
  `performer_1` smallint(4) NOT NULL,
  `performer_2` smallint(4) NOT NULL,
  `title` varchar(60) NOT NULL DEFAULT 'Unknown',
  `speed_1` tinyint(2) NOT NULL,
  `speed_2` tinyint(2) NOT NULL,
  PRIMARY KEY (`ass_ID`),
  UNIQUE KEY `number` (`number`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Associations with performer and record company' AUTO_INCREMENT=2 ;

--
-- Dumping data for table `association`
--

INSERT INTO `association` (`ass_ID`, `number`, `company`, `performer_1`, `performer_2`, `title`, `speed_1`, `speed_2`) VALUES
(1, '1234', 1, 1, 2, 'La Traviata', 78, 75);

I tried you suggestion Kyle and it worked but I realised it goes through every item to the end even if it has already found my item. I also still needed to find the ID and link that to the performer and this made me go back to Google. I must have used a different search phrase as I found array_search which will give me the key when it finds my item and I can then use that to find my ID.
It does not work on my array but I found useful user submitted function on the php.net page that will do what I want.

I now have:


// Search function
function recursive_array_search($needle,$haystack) {
     foreach($haystack as $key=>$value) {
         $current_key=$key;
         if($needle===$value OR (is_array($value) && recursive_array_search($needle,$value) !== false)) {
             return $current_key;
         }
     }
     return false;
 }

// Validate performer and insert any new performers into the database.
if (!empty($_REQUEST['performer_A1'])) {
	$data = trim($_REQUEST['performer_A1']);
	if ( recursive_array_search( $data, $result_performer ) ) {
	$per_ID = $result_performer[recursive_array_search( $data, $result_performer )]['per_ID'];
	}
	else { 
	// Insert the new artist into the database and get the ID for use later
	try { 
	$sth = $PDO->prepare("INSERT INTO performer (full_name) VALUES ('$data')");
	$sth->execute();
	//echo "Last insert ID = ".$PDO->lastInsertId();
	$per_ID = $PDO->lastInsertId();
	}  
	// Any errors? If so display error and write to error log specified.
	catch(PDOException $e) {  
	echo "I'm sorry, there seems to be a problem.";  
	// Write errors to the file
	//file_put_contents('PDOErrors.txt', "Error: ".$e->getMessage().PHP_EOL."File: ".$e->getFile().PHP_EOL."Line: ".$e->getLine().PHP_EOL, FILE_APPEND);
	// Display the error message when debugging - comment out 
	print "Error!: " . $e->getMessage() . "<br/>";
	} 
	}
}
else { $errors[] = 'You forgot to enter an artist A1.'; }

So reading this, you are doing it incorrectly. You should write your sql statement so that it will return only matches records, not so that you return a bigger group and then do a check in PHP (your using in_array()). So a query like “select id, fname, lname, email from users where fname = ‘foo’ and lname = ‘bar’”. From there you do a check on how many records are returned, if 0, no record found, if more than one, you most likely have a problem you should handle. Make sense? I can’t think of a situation where you should have to iterate through your return for a name on the PHP side.

If the names are unique and you don’t care about breaking cross db compatibility you can use MySQL’s REPLACE INTO statement to insert the name.

So reading this, you are doing it incorrectly. You should write your sql statement so that it will return only matches records, not so that you return a bigger group and then do a check in PHP (your using in_array()). So a query like “select id, fname, lname, email from users where fname = ‘foo’ and lname = ‘bar’”. From there you do a check on how many records are returned, if 0, no record found, if more than one, you most likely have a problem you should handle. Make sense? I can’t think of a situation where you should have to iterate through your return for a name on the PHP side.

Yes that makes sense @K.Wolfe ; its funny how you get something in your head and go down a path without considering something else.


query("select per_ID, name from performers where name = $data");

If it is true set $per_ID to the value returned from the query.
If this is 0 go through the part of code to insert the performer

This will save me generating the array and searching through the array!

I will check that option out later @Michael_Morris ;