PHP Function With MySQLi Query

I have never actually written a function so I am not sure how to proceed.

I am trying to write a function that shows how many results there are in the database for a certain city.

I have a MySQLi query that returns the results properly. This is the code for that:

City (
<?

if ($result = mysqli_query($link, "SELECT * FROM (units 
                JOIN unituserdefinedvalues 
                ON units.unitid = unituserdefinedvalues.unitid)
		WHERE units.propid ='2'
		AND units.city = 'City'
		AND unituserdefinedvalues.userdefinedid ='9'
		AND unituserdefinedvalues.value ='Yes'")) {

$row_cnt = $result->num_rows;

printf("%d \n", $row_cnt);

$result->close();


} else {
    print("0");
}



dbClose($link);
?>
)

Code (markup):

The results look like: City (10)


What I would like is to have a function where I could just add the following with the correct results:

City (<?php property_count("City"); ?>)

Code (markup):

I feel like it should be something like this:

function property_count($city)
	{
	$result = mysqli_query($link, "SELECT * FROM (units 
                JOIN unituserdefinedvalues 
                ON units.unitid = unituserdefinedvalues.unitid)
		WHERE units.propid ='2'
		AND units.city = $city
		AND unituserdefinedvalues.userdefinedid ='9'
		AND unituserdefinedvalues.value ='Yes'")) {

$row_cnt = $result->num_rows;

printf("%d \n", $row_cnt);

$result->close();


} else {
    print("0");
}	
		
		
} // end function property_count

Code (markup):

But that is giving me an error. I am sure that after starring at it for the past hour, it is something simple, but I am just not seeing it.

I appreciate any thoughts.

 

Try quotes around $city in the query. You had them before you put in the variable.

AND units.city = '$city'

Code (markup):

 

Thanks austin. But that did not do the trick. The error I am getting is:

Warning: mysqli_query() expects parameter 1 to be mysqli, null given in xxxxx...

Code (markup):

 

What does var_dump($link) have to say ?

 

Hey ActiveFrost. Excuse the ignorance, but I don't know what that is or how to do it.

 

Maybe this:

AND units.city = '" . $city . "'

Code (markup):

 

var_dump prints out all information there is about a variable.

Have a look here for examples:
var_dump

 

Ok. Thanks for helping with the var_dump. It is showing the correct city whenever i change the variable.

ie.
string(13) "Moncks Corner"
string(7) "Awendaw"


Sorry MyVodaPhone but your suggestion did not work. It seems for some reason that the query is producing no results based on the error. But like I said, outside of the function, it works properly...

 

function property_count($city)
{
	if ($result = mysqli_query($link, "SELECT * FROM (units JOIN unituserdefinedvalues ON units.unitid = unituserdefinedvalues.unitid) WHERE units.propid ='2' AND units.city = $city AND unituserdefinedvalues.userdefinedid ='9' AND unituserdefinedvalues.value ='Yes'")) {

		$row_cnt = $result->num_rows;

		printf("%d \n", $row_cnt);

		$result->close();

	} else {

    	print("0");

	}	
				
}

PHP:

 

Hey ActiveFrost.

That still isn't doing it. Still getting the "Warning: mysqli_query() expects parameter 1 to be mysqli, null given in..." error

 

Show us the line where $link is being initialized - I bet the problem isn't in the code you posted but have something to do with the actual connection handler.

 

function dbConnect() {

    $link = mysqli_init( );



    if (!$link->options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {
       die('Setting MYSQLI_INIT_COMMAND failed');
    }


    $link->ssl_set(NULL, NULL, 'xxxx.pem', NULL, NULL);


    $link->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);


    if(!$link->real_connect('xx.xxxxxxxx.com', "xxxxxx", 'xxxxxx', 'xxxxxx', 3306, NULL, MYSQLI_CLIENT_SSL)){

	die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());

    }


    if (!$link) {
       printf("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error());
       exit;
    }

    return $link;

}




function dbClose($link) {
    mysqli_close($link);
}


$link = dbConnect();

PHP:

 

Change your function to:

function property_count($city)
{
echo "SELECT * FROM (units JOIN unituserdefinedvalues ON units.unitid = unituserdefinedvalues.unitid) WHERE units.propid ='2' AND units.city = $city AND unituserdefinedvalues.userdefinedid ='9' AND unituserdefinedvalues.value ='Yes'";

    if ($result = mysqli_query($link, "SELECT * FROM (units JOIN unituserdefinedvalues ON units.unitid = unituserdefinedvalues.unitid) WHERE units.propid ='2' AND units.city = $city AND unituserdefinedvalues.userdefinedid ='9' AND unituserdefinedvalues.value ='Yes'")) {

        $row_cnt = $result->num_rows;

        printf("%d \n", $row_cnt);

        $result->close();

    } else {

        print("0");

    }  
               
}

PHP:

Copy the query to your database front (if you are using phpmyadmin or command prompt) and see if it gives you the correct result.