...

View Full Version : mysql_query in custom function



Philwn
02-28-2012, 10:54 AM
Hi,
Ive been learning php over the last year and I am starting to venture into functions to help keep my code clean an easier to manage.
Every page on my site will have a heading, subheading and content(body) so i thought why not create a function which runs the query for that page and stores these into variables. however it doesnt seem to be working. my db_connect and db_close functions work but not cont_qry() any help greatly appreciated.


<?php
session_start();
function db_connect() {
global $db_link;

@$db_link = mysql_connect('localhost', '', '');

if ($db_link) @mysql_select_db('');
return $db_link;
}

function db_close() {
global $db_link;

$result = mysql_close($db_link);

return $result;
}

function cont_qry($NAME) {
db_connect();
$qry = mysql_query("SELECT HEADING, SUBHEADING, BODY FROM page_content WHERE NAME='$NAME'");
while($row = mysql_fetch_array($qry))
{
$HEADING = $row['HEADING'];
$SUBHEADING = $row['SUBHEADING'];
$BODY = $row['BODY'];
}
mysql_free_result($qry);
db_close();
return $HEADING;
return $SUBHEADING;
return $BODY;
}
?>

Philwn
02-28-2012, 11:04 AM
I have figured it out now, for anyone else struggling here is my answer


<?php
session_start();
function db_connect() {
global $db_link;

@$db_link = mysql_connect('', '', '');

if ($db_link) @mysql_select_db('');
return $db_link;
}

function db_close() {
global $db_link;

$result = mysql_close($db_link);

return $result;
}

function cont_qry($NAME) {
db_connect();
@ $qry = mysql_query("SELECT HEADING, SUBHEADING, BODY FROM page_content WHERE NAME='$NAME'");
while($row = mysql_fetch_array($qry))
{
global $HEADING;
global $SUBHEADING;
global $BODY;
$HEADING = $row['HEADING'];
$SUBHEADING = $row['SUBHEADING'];
$BODY = $row['BODY'];
}
mysql_free_result($qry);
db_close();
}
?>

Dormilich
02-29-2012, 08:46 AM
there is no need to use $db_link as global. mysql_* functions automatically use the last open connection.

but if you're at such a structure, consider using MySQLi (http://php.net/mysqli) or PDO (http://php.net/pdo) objects over the outdated mysql extension.

beside that, while the @ operator conveniently suppresses error messages on the production server, it hinders you in development (where you need those messages) and it slows PHP down. additionally there is no error handling which will sooner or later lead to the most common PHP/MySQL error and I also see nothing to prevent SQL Injections.

Philwn
03-01-2012, 12:27 PM
Thanks for your input hadnt noticed anyone had replied.

This is what I now have:

<?php
session_start();
function db_connect() {

$db_link = mysql_connect();

if ($db_link) @mysql_select_db('');
return $db_link;
}

function db_close() {

$result = mysql_close($db_link);

return $result;
}

function cont_qry($NAME) {
db_connect();
$NAME = mysql_real_escape_string($NAME);
$qry = mysql_query("SELECT HEADING, SUBHEADING, BODY, META_DESC, META_KEYS, PAGE_TITLE FROM page_content WHERE NAME='$NAME'");
$num_rows = mysql_num_rows($qry);
IF ($num_rows==0){
header ("location: error.php");
}
while($row = mysql_fetch_array($qry))
{
global $HEADING;
global $SUBHEADING;
global $BODY;
global $PAGE_TITLE;
global $META_KEYS;
global $META_DESC;
$HEADING = $row['HEADING'];
$SUBHEADING = $row['SUBHEADING'];
$BODY = $row['BODY'];
$META_DESC = $row['META_DESC'];
$META_KEYS = $row['META_KEYS'];
$PAGE_TITLE = $row['PAGE_TITLE'];
}
mysql_free_result($qry);
db_close();
}
?>

Dormilich
03-01-2012, 01:19 PM
marginally better.

there is still no error handling (what if your query fails?) and there is still the issue with the globals. do not use globals! they are a nightmare to debug and maintain (what if some external function changes the value so your code will fail?). if there is absolutely no way around at least use the superglobal $GLOBALS.

Philwn
03-02-2012, 02:59 PM
im new to using functions but the in my php script i call this function and without declaring the variables as global I cannot use the values stored in the variables outside of the function.
would this be done using return instead?

Dormilich
03-02-2012, 03:51 PM
you can pass in variables as parameters and get output parameters via return.


function query($conn, $data)
{
// prepare SQL string
$sql = ... ;
$res = mysql_query($sql, $conn);

if (false === $res)
{
throw new UnexpectedValueException("Error on SQL: '$sql'" . PHP_EOL . mysql_error());
}
// let another function handle the data fetching
return $res;
}

Philwn
03-05-2012, 12:16 PM
I may be missing something(not fully understanding) here but i have merged your code into my function. I have then called the function cont_qry('about') and recieved a blank page, so i tried echo 4res and i get undefined variable. how could I use the information it returns without putting them in global variables?


<?php
session_start();
function db_connect() {

$db_link = mysql_connect('localhost', '', '');

if ($db_link) mysql_select_db('');
return $db_link;
}

function db_close() {

$result = mysql_close();

return $result;
}

function cont_qry($NAME) {
db_connect();
$NAME = mysql_real_escape_string($NAME);
$sql = "SELECT HEADING, SUBHEADING, BODY, META_DESC, META_KEYS, PAGE_TITLE FROM page_content WHERE NAME='$NAME'";
$res = mysql_query($sql);
if (false === $res)
{
throw new UnexpectedValueException("Error on SQL: '$sql'" . PHP_EOL . mysql_error());
}
// let another function handle the data fetching
return $res;
db_close();
}
?>

<!--test the function -->
<?php cont_qry('about');
echo $res;
?>

Dormilich
03-05-2012, 01:07 PM
I may be missing something(not fully understanding) here but i have merged your code into my function. I have then called the function cont_qry('about') and recieved a blank page, so i tried echo 4res and i get undefined variable. how could I use the information it returns without putting them in global variables?


<!--test the function -->
<?php cont_qry('about');
echo $res;
?>

you need to assign the return value, otherwise it is dumped into the void. and since variables ain't accessible outside their Scope, you have an undefined variable there.

$res = cont_qry('about');
var_dump($res);

PS. I'm not sure if it will work if you close the connection before you fetch the data.

Philwn
03-06-2012, 09:53 AM
var_dump just gives me
resource(3) of type (mysql result)

Dormilich
03-06-2012, 01:42 PM
that is supposed to be correct.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum