Run a query if 0 results are retuned

lurtnowski · December 11, 2022, 2:16am

I have 2 JNSERT queries…
I want 1 to run if a SELECT query returns 0 results using

//check if this power strip has a receptacle
try{
    $sql = "SELECT receptacle_id FROM power_trails WHERE power_strip_id = ".$power_strip_id;   
	echo $sql;
	
    $result = $pdo->query($sql);
    if($result->rowCount() != 0){
        while($row = $result->fetch()){
            $receptacle_id = $row['receptacle_id'];
						
			 try{
				$sql = "INSERT INTO power_trails (receptacle_id,power_strip_id,outlett_placement,".$key.") VALUES (".$receptacle_id.",".$power_strip_id.",".$Outlett.",".$asset_id.")";
				$pdo->exec($sql);
				header("location: add_power_strip_success.php?id=".$asset_id."&type=".$_POST['type']);
			
			} catch(PDOException $e){
				die("ERROR: Could not able to execute $sql. " . $e->getMessage());
			}                
        }
        // Free result set
        unset($result);
    } else {
	$sql = "INSERT INTO power_trails (power_strip_id,outlett_placement,".$key.") VALUES (".$power_strip_id.",".$Outlett.",".$asset_id.")";
	$pdo->exec($sql);
	header("location: add_power_strip_success.php?id=".$asset_id."&type=".$_POST['type']);
	}
	
} catch(PDOException $e){
    die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

the query (when I run it in PHPMyAdmin seems to work, but returns nothing

Since no receptacle_id is found, why is this the result

Array
(
    [power_strip_id] => 1
    [Outlett] => 2
    [type] => KVM
    [asset_id] => 1
)
SELECT receptacle_id FROM power_trails WHERE power_strip_id = 1
ERROR: Could not able to execute INSERT INTO power_trails (receptacle_id,power_strip_id,outlett_placement,kvm_id) VALUES (,1,2,1). SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1,2,1)' at line 1

shouldnt the other INSERT query be run if the query returns nothing?

mabismad · December 11, 2022, 2:48am

If you read the documentation for the rowCount() method, you will find that it may or may not work for SELECT queries.

If you want to find the number of matching rows, use a SELECT COUNT(*) … query, then fetch and use the count value.

lurtnowski · December 11, 2022, 3:05am

but if I use the COUNT(*) in my SELECT, wouldnt it return the number of receptacles found? A power stri p would either have 1 or not (1,0) then how would I get the receptacle_id if one is found?

mabismad · December 11, 2022, 3:36am

Here’s a different approach, since you want to use data if a row is found. Just fetch/test if there was fetched data.

if($row = $result->fetch())
{
    // a row was found
}

Next, why are you currently using a loop to fetch what will be a most one row of data? Why aren’t you using proper prepared queries? Why are you redirecting all around on your site (this opens your site to phishing attacks)? The only redirect your form processing code should have, should be upon successful completion, and it should be to the exact same url of the current page to cause a get request for that page. Why don’t you have an exit/die statement to stop code execution at any redirect?

lurtnowski · December 11, 2022, 4:23am

like this?

try{
    $sql = "SELECT receptacle_id FROM power_trails WHERE power_strip_id = :power_strip_id";   
	//echo $sql;
	
    $stmt = $pdo->prepare($sql);
	
	$stmt->execute(['power_strip_id' => $power_strip_id]);
	
    if($row = $result->fetch()){
		
      $receptacle_id = $row['receptacle_id'];
						
		try{
		
			$sql = "INSERT INTO power_trails (receptacle_id,power_strip_id,outlett_placement,".$key.") VALUES (".$receptacle_id.",:power_strip_id,:Outlett,:asset_id)";
				
			$stmt = $pdo->prepare($sql);
			
			$stmt->execute(['power_strip_id' => $power_strip_id,'outlett' => $Outlett,'asset_id' => $asset_id]);
							
			header("location: add_power_strip_success.php?id=".$asset_id."&type=".$_POST['type']);
			die();
		} catch(PDOException $e){
			die("ERROR: Could not able to execute $sql. " . $e->getMessage());
        }
    } else {
		
	  $sql = "INSERT INTO power_trails (power_strip_id,outlett_placement,".$key.") VALUES (:power_strip_id,:Outlett,:asset_id)";
	
	  $stmt = $pdo->prepare($sql);
			
	  $stmt->execute(['power_strip_id' => $power_strip_id,'outlett' => $Outlett,'asset_id' => $asset_id]);
	  
	  header("location: add_power_strip_success.php?id=".$asset_id."&type=".$_POST['type']);
	  die();
	}
	
} catch(PDOException $e){
    die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}  
}
?>

Thallius · December 11, 2022, 10:12am

Why do you insert a recepable_id one time and in the other times not? In Case of database design this seems to be a wrong design for me.

r937 · December 11, 2022, 11:46am

i’m having trouble understanding what you’re trying to do, because you never explained what you’re trying to do, you just left it to us to figure it out from your code

are you perhaps trying to do an upsert?

m_hutley · December 11, 2022, 12:21pm

So there’s a couple of ways or doing what you’re trying to do.

Rudy: Basically what he’s trying to do is

INSERT  INTO power_trails (receptacle_id,power_strip_id,outlett_placement,whateverfieldisinkey)
VALUES (
          (
            SELECT receptacle_id 
            FROM   power_trails 
            WHERE  power_strip_id = :power_strip_id
            LIMIT  1
          ),
          :power_strip_id,
          :outlet_placement
          :whatevervalue
)

And trying to use PHP to handle the case where the SELECT has no result.

(As for WHY you would do that instead of putting all the data in one row… shrug)

r937 · December 11, 2022, 12:29pm

LIMIT 1???

[backs away slowly]

m_hutley · December 11, 2022, 12:32pm

lol. Well, you could aggregate, but based on his request, there could/will be multiple rows that match the select, all with the same receptacle_id (at least, they better have the same receptacle_id, or this whole logic pattern falls apart. Again.)

pandglobal · December 11, 2022, 12:41pm

Okay i will simple do this.

$search = $con->prepare ("SELECT * FROM power_trails WHERE power_stripe_id = ?");
$search->bindParam(1, $idtofind, PDO::PARAM_STR);
$search->execute();
$result = $search->fetch(PDO::FETCH_ASSOC);

if(empty($result['power_stripe_id']){
$insert = $con->prepare ("INSERT INTO power_trails(power_trails,leftcont)VALUES(?,?)");
$insert->bindParam (1, $idtofind, PDO::PARAM_STR);
$insert->bindParam (2, $leftcont, PDO::PARAM_STR);
$insert->execute ();
}

This is mainly needed if you want to first query to know if such id already exists in the database and avoid entering it multiple times.
I use this method alot for some reason best know to me

Thallius · December 11, 2022, 2:55pm

For this you should use the insert ignore or on duplicate update function

INSERT IGNORE INTO table (…) VALUES (…)

Will only insert if the primary key does not already exists in the table. Of course it’s up to you to set the right primary keys. In this Case it look like the power_strike_id should be the primary.

INSERT INTO table (…) VALUES (…) ON DUPLIKATE KEY UPDATE SET ….

will not enter a new row if the primary key already exists but change the columns you specify with SET

pandglobal · December 11, 2022, 4:22pm

Yes mostly if the column is set to primary key, in most cases they are not primary keys.
So i search before inserting

pandglobal · December 11, 2022, 4:26pm

perfect! but won’t this ignore functions check to see if all values matches? or it will only update values where the values is different from individual columns?

lurtnowski · December 11, 2022, 9:44pm

k, ill explain what my goal is…
have a table called power_trails

CREATE TABLE power_trails (
   power_trail_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   power_strip_id SMALLINT UNSIGNED,
   outlett_placement TINYINT UNSIGNED,
   receptacle_id SMALLINT UNSIGNED,
   circuit_breaker_id SMALLINT UNSIGNED,
   ups_id TINYINT UNSIGNED,
   pdu_id TINYINT UNSIGNED,
   power_panel_id SMALLINT UNSIGNED,
   new_id SMALLINT UNSIGNED NULL,
   periphial_storage_id SMALLINT UNSIGNED NULL,  
   server_id SMALLINT UNSIGNED NULL,  
   network_panel_id SMALLINT UNSIGNED NULL,  
   network_standard_id SMALLINT UNSIGNED NULL,  
   monitor_id SMALLINT UNSIGNED NULL,  
   kvm_id SMALLINT UNSIGNED NULL,  
   chassis_id SMALLINT UNSIGNED NULL,  
   operational_status BOOLEAN DEFAULT 1,
   FOREIGN KEY ( new_id ) REFERENCES news( new_id ),
   FOREIGN KEY ( periphial_storage_id ) REFERENCES periphial_storages( periphial_storage_id ),
   FOREIGN KEY ( server_id ) REFERENCES servers( server_id ),
   FOREIGN KEY ( network_panel_id ) REFERENCES network_panels( network_panel_id ),
   FOREIGN KEY ( network_standard_id ) REFERENCES network_standards( network_standard_id ),
   FOREIGN KEY ( monitor_id ) REFERENCES monitors( monitor_id ),
   FOREIGN KEY ( kvm_id ) REFERENCES kvms( kvm_id ),
   FOREIGN KEY ( chassis_id ) REFERENCES chassises( chassis_id ),
   FOREIGN KEY ( power_strip_id ) REFERENCES power_strips( power_strip_id ),
   FOREIGN KEY ( receptacle_id ) REFERENCES receptacles( receptacle_id ),
   FOREIGN KEY ( circuit_breaker_id ) REFERENCES circuit_breakers( circuit_breaker_id ),
   FOREIGN KEY ( power_panel_id ) REFERENCES power_panels( power_panel_id ),
   FOREIGN KEY ( pdu_id ) REFERENCES pdus( pdu_id ),
   FOREIGN KEY ( ups_id ) REFERENCES upss( ups_id ),
   PRIMARY KEY ( power_trail_id )
);

I created an new asset and now want to give it power.
I lugged it in to a power strip/outlet to start a power trail

That works…
But Id like to add a receptacle to it.
I want to add the receptacle but also have it so that if another started power trail uses the same power strip (power_strip_id) it would automatically get the same receptacle (receptacle_id) as the first since they use the same power strip.

lurtnowski · December 14, 2022, 9:21pm

I got

try{
    $sql = "SELECT receptacle_id FROM power_trails WHERE power_strip_id = ?";   
	//echo $sql;
	
    $search = $pdo->prepare($sql);
	
	$search->bindParam(1, $power_strip_id, PDO::PARAM_STR);
	
	$search->execute();
	
	$result = $search->fetch(PDO::FETCH_ASSOC);
		
	if(empty($result['receptacle_id'])) {
		
	  $sql = "INSERT INTO power_trails (power_strip_id,outlett_placement,".$key.") VALUES (?,?,?)";
		
	  $insert = $pdo->prepare($sql);
	  
	  $insert->bindParam (1, $power_strip_id, PDO::PARAM_STR);
	  
	  $insert->bindParam (2, $Outlett, PDO::PARAM_STR);
	  
	  $insert->bindParam (3, $asset_id, PDO::PARAM_STR);
 
	  $insert->execute ();
	  
    } else {
						
	  $sql = "INSERT INTO power_trails (receptacle_id,power_strip_id,outlett_placement,".$key.") VALUES (?,?,?,?)";
		
	  $insert = $pdo->prepare($sql);
	  
	  $insert->bindParam (1, $receptacle_id, PDO::PARAM_STR);
	  
	  $insert->bindParam (2, $power_strip_id, PDO::PARAM_STR);
	  
	  $insert->bindParam (3, $Outlett, PDO::PARAM_STR);
	  
	  $insert->bindParam (4, $asset_id, PDO::PARAM_STR);
 
	  $insert->execute ();


	}

header("location: add_power_strip_success.php?id=".$asset_id."&type=".$_POST['type']);
	
} catch(PDOException $e){
    die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

So the logim seems ok, I don’t see why the receptacle_id doesnt carry over,If I start a power trail by assi gn ing a power strip to a receptacke l ike

If I start another power trail using the same power strip
, why is the receptacle NULL, shouldnt it get the same receptacle as the f irst power trail

system · March 17, 2023, 1:55pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.