PHP - Php Function To Extra Single Value From Query

Hey, I was wondering if there is a way to pull multiple rows at once using a list of unique identifiers. For example, I want to pull the rows with the IDs of 4,13,91 and 252

I know the WHERE part of this query is incorrect, but I'm putting it to hopefully help you guys understand what I'm looking for.

$result = mysql_query("SELECT * FROM $table WHERE id='4' OR '13' OR '91' OR '252'");
while($row = mysql_fetch_array($result))
{
echo($row['name']);
}


Or is the best way simply to do it one query at a time without a while statement? There could be as many as a few dozen records being pulled per page.

I'm so sorry for this question but I not really know how to play with single and double quote.

If I have a query like this:

Code: [Select]
  mysql_query('UPDATE table SET Status=1,Sending=Done WHERE ID IN ('.implode(',', $done).')');
And I wish to add Code: [Select]
SentAt='$date' in the query as well , and I try this:

Code: [Select]
  mysql_query('UPDATE table SET Status=1,Sending=Done,SentAt='$date' WHERE ID IN ('.implode(',', $done).')');
Not working...how should I write it?

Thank you.

I'm using the codeigniter mvc framework and there's an escape function to use before adding the data to the database. This function adds single quotes around the string of data. Is there any already existing php function or does anyone know how to code a function that strips ONLY the surrounding single quotes?

PHP script return 20 UL LIST values like,

< ul >

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T

< /ul >

How to display UL LIST into row wise 5 columns like

A B C D
E F G H
I J K L
M N O P
Q R S T (20 ITEMS)

Edited December 7, 2019 by aveeva

I've followed the PHP Freaks pagination tutorial which you can find here.

And I also got it to work, the only problem is that the script won't work when I use the query outside of the function.

Here's the function:



<?php 
function knuffix_list ($query, $dbc) {

// find out how many rows are in the table:
$query_row = "SELECT COUNT(*) FROM con";
$query_run = mysqli_query ($dbc, $query_row);
$row = mysqli_fetch_row($query_run);
$num_rows = $row[0];

// number of rows to show per page
$rows_per_page = 5;

// find total pages -> ceil for rounding up
$total_pages = ceil($num_rows / $rows_per_page);

// get the current page or set a default
if (isset($_GET['current_page']) && is_numeric($_GET['current_page'])) {

// make it an INT if it isn't

$current_page = (int) $_GET['current_page'];

} else {

// default page number

$current_page = 1;

}


// if current page is greater than total pages then set current page to last page
if ($current_page > $total_pages) {

$current_page = $total_pages;

}

// if current page is less than first page then set current page to first page
if ($current_page < 1) {

$current_page = 1;

}

// the offset of the list, based on current page
$offset = (($current_page - 1) * $rows_per_page);

echo "test " . $query;

// SCRIPT ONLY WORKS IF I INSERT QUERY HERE

$query

= "SELECT * FROM con, user WHERE con.user_id = user.user_id ORDER BY contributed_date DESC LIMIT $offset, $rows_per_page";

$data = mysqli_query ($dbc, $query) or die (mysqli_error ($dbc));

// Loop through the array of data

while ($row = mysqli_fetch_array ($data)) {

global $array;

// Variables for the table

$con_id = $row['con_id'];

$likes_count = $row['likes'];

$dislikes_count = $row['dislikes'];

$dbuser_name = $row['nickname'];

$dbuser_avatar = $row['avatar'];

$user_id = $row['user_id'];

// The TABLE

echo "<table padding='0' margin='0' class='knuffixTable'>";

echo "<tr><td width='65px' height='64px' class='avatar_bg' rowspan='2' colpan='2'><img src='avatar/$dbuser_avatar' alt='avatar' /></td>";

echo "<td class='knuffix_username'><strong><a href='profile.php?user=$dbuser_name' title='Profile of $dbuser_name'>" . $dbuser_name . "</a> ___ " . $user_id . "____ <form action='' method='POST'><button type='submit' name='favorite' value='fav'>Favorite</button></form>";

echo "</strong><br />" . $row['category'] .  " | " . date('M d, Y', strtotime($row['contributed_date'])) . "</td></tr><tr><td>";

echo "<form action='' method='post'>

<button class='LikeButton' type='submit' name='likes' value='+1'>Likes</button>

<button class='DislikeButton' type='submit' name='dislikes' value='-1'>Dislikes</button>

<input type='hidden' name='hidden_con_id' value='" . $con_id . "' />

</form></td><td class='votes'>Y[" . $likes_count . "] | N[" . $dislikes_count . "]</td></tr>";

echo "<tr><td class='knuffix_name' colspan='3'><strong>" . htmlentities($row['name']) . "</strong><br /></td></tr>";

echo "<tr><td colspan='2' class='knuffix_contribution'><pre>" . $row['contribution'] . "</pre><br /></td></tr>";

echo "</table>";

// POST BUTTONS inside the table

$likes = $_POST['likes'];

$dislikes = $_POST['dislikes'];

$con_id = $_POST['hidden_con_id'];

$favorite = $_POST['favorite'];

$array = array ($likes, $dislikes, $con_id, $user_id, $favorite);

}

/********* build the pagination links *********/

// BACKWARD
// if not on page 1, show back links and show << link to go back to the very first page
if ($current_page > 1) {

echo " <a href='{$_SERVER['PHP_SELF']}?current_page=1'><<</a> ";

// get previous page number and show < link to go to previous

$prev_page = $current_page - 1;
 

echo " <a href='{$_SERVER['PHP_SELF']}?current_page=$prev_page'><</a> ";

}

// CURRENT
// range of number of links to show
$range = 3;

// loop to show links in the range of pages around current page
for ($x = ($current_page - $range); $x < (($current_page + $range) + 1); $x++) {

// if it's a valid page number...

if (($x > 0) && ($x <= $total_pages)) {

// if we're on current page

if ($x == $current_page) {

// highlight it but don't make a link out of it

echo "[<b>$x</b>]";

// if it's not the current page then make it a link

} else {

echo "<a href='{$_SERVER['PHP_SELF']}?current_page=$x'>$x</a>";

}

}
}


// FORWARD
// if not on the last page, show forward and last page links
if ($current_page != $total_pages) {

// get next page

$next_page = $current_page + 1;

// echo forward link for next page

echo " <a href='{$_SERVER['PHP_SELF']}?current_page=$next_page'>></a> ";

// echo forward link for last page

echo " <a href='{$_SERVER['PHP_SELF']}?current_page=$total_pages'>>></a> ";

}

/***** end building pagination links ****/

mysqli_close($dbc);

}

?>


As you can see above, the script will only work if I put the query right below the $offset variable and above the $data variable.

I put the test echo above the query to see how the query looks like when I induce the query from the outside through the function parenthesis into the function, and this is what I get printed out:


test SELECT * FROM con, user WHERE con.user_id = user.user_id ORDER BY contributed_date DESC LIMIT ,


Obviously the $offset and the $rows_per_page variables are not set, when I induce the query from the outside into the function.

So in that sense my question is:

How can I induce the query from the outside into the function SO THAT the $offset and the $rows_per_page variables are set as well and NOT empty?


p.s.

I need the query outside of the function because I'm using a sort by category functionality.

Hi, I've been scratching my head for a while now about how to do this, I'm relatively new to php and mysql and perhaps foolishly taking on creating a user area for a website.  I have everything else working, all of my register account functions and confirmations and all of the login scripts etc. 

I have created a profile page which returns various information to the user (this bit works fine) and I've got some nice show/hide toggles running with some javascript/css but my intention is to allow the user to change thier information (e-mail address, contact phone number and also whether they are subscribed to the e-mail list), it also displays any support tickets or messages.

So after the long intro, here's what I'm struggling with...  I have a form in a visibility toggled <div> which submits a 'change_email' script, so a user wants to change their e-mail, clicks on change, the <div> appears, they bang in the new e-mail and hit submit.

My php script appears to work (because it doesn't throw up any errors), until you realise that actually it's not updated the record in the db...

I'm using mysql_query("UPDATE users SET email='$new_email' WHERE username='$user'");

Do I need to setup variables for all of the information in the db (name, username, password, email, contno etc etc) and include them in the command to get it to work or should that just pick the correct record and then update it? If that is the case is there a way I can include 'blank' variables so I don't have to set them all up...

e.g.  mysql_query("UPDATE users SET user='',password='',email='$new_email', etc WHERE username='$user'");

Many thanks in anticipation

Hey guys I am stuck on how to best create a php function to do the following.   I need to echo out 4 restaurants.  I have a database of numerous restaurant categories: breakfast, lunch, dinner, and coffee.  In this database some categories have more results than others.   Basically I want to randomly choose results from each category query, Breakfast, Lunch, and dinner to create 4 echoed results. (I understand that one category will have an extra result as there are 3 categories and 4 echoed results.)  I do not want one category of restaurant to bump out another category because it has more results.  Also, if there are not enough or no results from one category, I want extra results from the other queries to make up for it so that there are always 4 echoed items.   Finally, if there are not enough results from breakfast, lunch, dinner to make 4 echoed items, I would like Coffee to fill the 4 required results.   Randomly echo 4 results from the below primary queries: Primary Query:  Breakfast Restraunts Query Primary Query:  Lunch Restraunts Query Primary Query:  Dinner Restraunts Query   Get results from secondary query if Primary queries can’t echo 4 results: Secondary Query:  Coffee Shops Query   I hope this makes sense.  I understand how to do the mysql queries, I just don't know how to do the php function to echo the results as required.  Any help would be appreciated!
Edited by jason360, 09 January 2015 - 06:53 PM.

I have a script that runs at the end of a batch process specifically built to clean out empty records.

How can I make it a function and then call the function.

Here is my query;

Code: [Select]
$sql="DELETE FROM ".$dbname." . property WHERE property . id=''";
mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

$sql="DELETE FROM ".$dbname." . agents WHERE agents . ip_source=''";
mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

$sql="DELETE FROM ".$dbname." . settings WHERE settings . prop_id=''";
mysql_query($sql) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $sql . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());

I would like this to be in a function that i could specifically call the function. Why? Well because I have 5 different sets of queries like this set above and I'm going to build a conditional. So like if condition 1 then run use this function, condition 2, use function ??? etc and so on.

Hey, I am coding this forum and the following PDO prepare query calls the topic details. The prepare query works perfectly well, however when I add it into a function within another class and then call it, the script does not seem to work.

I get errors like the following:

Fatal error: Call to a member function fetch() on a non-object in C:\wamp\www\new\sources\forum\new.php on line 117

The prepare query fails to excute, this is because I do not know how to add it into a function.

This is what I tried:

Code: [Select]
class FUNC {
function userDetails($table, $column, $cVaulue, $oBy, $ASDSC)
{
global $dbh;

$sth = $dbh->prepare('SELECT * FROM `'.$table.'` WHERE `'.$column.'` = '.$cVaulue.' ORDER BY `'.$oBy.'` '.$ASDSC.'');
$sth->bindParam(':id', $id, PDO::PARAM_INT);
$sth->execute();

}
}


The FUNC class is called as the $load variable, and in my forum class I have decalred it in my global so the variable can be passed:

Code: [Select]
$id = $forum_data['id'];

$load->userDetails('db_topics', 't_forum_id', ':id', 't_whenlast', 'DESC');
$coll=$sth->fetch(PDO::FETCH_ASSOC);
$this->html->RightForum($coll);


The code is very silly, but the fact is I did not know how to do this.. Please help me put this query into a function because I need to make use of it in many other parts of the forum and I don't want to constantly declare this query.

Hello I have a general php insert function that I want to use to insert data into tables regulary, the problem is, each time I use it it creates a new row in the table instead of using certain columns to add information to, here is the function, can you tell me why does it insert a new row each time I use it?

class Query{
function add($table,$cols,$vals){
		include 'conx.php';
		$query = "
			INSERT INTO $table($cols) VALUES('$vals');
		";
		
		$res = mysqli_query($con,$query) ;
		
		if($res){
			echo "Inserted value";
		}else{
			echo "Did not insert value";
		}
	}
}

Hi,
Doing a Query SELECT * FROM table WHERE field BETWEEN low_number AND high_number

If i have three rows with three numbers: IE: 27, 50, 80.
and i run the query above, it returns a result of:
27 and 50.

How can i get it to return a result of:
27,50, and 80.??

WHY:
age range search: i want to return an age range of people = and between the age of say: 20 and 70 but i want to include the 20 and 70.
How can i do this?

I want a simple function that converts a sql query into two queries for pagination purposes.

So if the sql query is "SELECT `Name` FROM table1"

I would like to perform:

"SELECT COUNT(*) FROM table 1"

and

"SELECT `Name` FROM table1 LIMIT 10, 20"

(example)

(1) Would this work for most basic queries? (obviously if LIMIT wasn't already used)?

(2) What command would I use to convert "SELECT `Name` FROM table1" to "SELECT COUNT(*) FROM table 1"

I am aware there is more involved than this, but am just interested in the query side of things.

Thanks in advance for help.
I have a PHP script that has a function called traverseXMLNodes and it passes an xml string. I want anyone with a username and password to be able to call this function from his website or application or programming. In short i want my site to act as an API(Application Programming Interface).

 I am really stuck as to how to go about this. There are other functions i would like to call as well so traverseXMLNodes  is not the only function i want to my "API" users to access.

Below is the code I am using. the filename for my php i called remote.php. I am using the following query string to test and no results are returned.
http://localhost/testfolder/remote.php?xmlstring=20


Code: [Select]
<?php
$allowedfuncs = array('xmlstring','remotefunction2');
 
if(in_array($_get['xmlstring'], $allowedfuncs))
{
    $func = $_get['xmlstring'];
    $output = traverseXMLNodes($func);        // This calls $_get['funccall']()
    echo $output;
}
else
{
    exit();
}
 
 function traverseXMLNodes ($func) {
 
 echo "The XML string is";
 echo "<br/>";
 echo $func;
 echo "<br/>";
 }
/*
More functions go here
*/
?>

I'm trying to build a search function that will search all columns in a table. Here is what I've got:

Code: [Select]
if (isset($_POST['submitted'])) {
$q = "SELECT * FROM children ";

if(isset($_POST['query'])) {
$q .= "WHERE ";

$all = "SELECT * FROM children WHERE 1";
$r_all = mysqli_query($dbc, $all);

while ($field = $r_all->fetch_field()) { // Get field headers for building query
$f_name = "{$field->name} LIKE {$_POST['query']},:"; // Build query with search string
$fn = explode(":",$f_name); // Need to convert $f_name to an array but this does not convert it to a single array but multiple arrays

$q .= implode("OR", $fn); // Need to insert OR for the db query

} // End while ($field = $r_all->fetch_field())

} // End if(isset($_POST['query']))

} // End if (isset($_POST['submitted']))

The explode function is what is not working for me. It gives me this (sam is the search string I entered into the form):

Code: [Select]
Array ( [0] => child_id LIKE sam, [1] => ) Array ( [0] => first_name LIKE sam, [1] => ) Array ( [0] => second_name LIKE sam, [1] => ) Array ( [0] => last_name1 LIKE sam, [1] => ) Array ( [0] => last_name2 LIKE sam, [1] => ) Array ( [0] => gender LIKE sam, [1] => ) Array ( [0] => dob LIKE sam, [1] => )

so instead of getting one array with each string being a value I get multiple arrays. Any ideas?

I've created a function getsub(). The idea is that this function contains a query that grabs records from the categories table, using the $row['cat_id'] that I pass to it from within an existing while ($row=mysql_fetch_assoc($res)) loop.
Heres the function:

function getsub($rowid,$catsort)
{
global $system, $LANGUAGES, $subres;
$subquery = "SELECT * FROM webid_categories
   WHERE parent_id = " . $rowid . "
   " . $catsort;
$subres = mysql_query($subquery);
$system->check_mysql($subres, $subquery, __LINE__, __FILE__);
return $subres;
}

And heres the code that calls this function and passes the $row['cat_id'] value to it:

// prepare categories list for templates/template
// Prepare categories sorting
if ($system->SETTINGS['catsorting'] == 'alpha')
{
$catsorting = ' ORDER BY cat_name ASC';
}
else
{
$catsorting = ' ORDER BY sub_counter DESC';
}

$query = "SELECT cat_id FROM " . $DBPrefix . "categories WHERE parent_id = -1";
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);

$query = "SELECT * FROM " . $DBPrefix . "categories
  WHERE parent_id = " . mysql_result($res, 0) . "
  " . $catsorting . "
  LIMIT " . $system->SETTINGS['catstoshow'];
$res = mysql_query($query);
$system->check_mysql($res, $query, __LINE__, __FILE__);

while ($row = mysql_fetch_assoc($res))
{
$subcats = getsub($row['cat_id'],$catsorting);
while($subrow = mysql_fetch_assoc($subcats){

$template->assign_block_vars('sublist', array(
'SID' => $subrow['cat_id'],
'SNAME' => $category_names[$getrow['cat_id']]
));
}

$template->assign_block_vars('cat_list', array(
'CATAUCNUM' => ($row['sub_counter'] != 0) ? '(' . $row['sub_counter'] . ')' : '',
'ID' => $row['cat_id'],
'IMAGE' => (!empty($row['cat_image'])) ? '<img src="' . $row['cat_image'] . '" border=0>' : '',
'COLOUR' => (empty($row['cat_colour'])) ? '#FFFFFF' : $row['cat_colour'],
'NAME' => $category_names[$row['cat_id']]
));
}

then a .tpl file just references the {sublist.SID} and {sublist.SNAME} variables.

BUT....

It ain't working. My page just goes blank.

Any help would be massively appreciated.