PHP - Querying Depending On Field Value In Db

Is it possible to use a 'SELECT FROM' against a query in a new query?

For example (and I know this would be bad practice but it is just a simple example so I can understand!!!), if you had a  table called 'staff' with this sort of set up

ID | NAME | SEX | AGE | DEPARTMENT

and this query
$staffquery = SELECT * FROM staff
would it be possible to then use

$males = SELECT * FROM $staffquery WHERE sex = 'Male'
$females = SELECT * FROM $staffquery WHERE sex = 'Female'
$males_in_despatch = SELECT * FROM $staffquery WHERE sex = 'Male' AND department = 'Despatch'

or would I have to run the full query each time? The example I have given is not a good one as it only queries one table anyway but if it was querying several joined tables then it would obviously be a lot of repeated code.

I am trying to set up a page which does various counts and sums from a query that links several different tables and I am trying to work out the best way to set it all up before worrying about actually getting the data.

I can post the full query that I am actually using and some sample data if required.

Thanks in advance
Steve

I have put together a query which is designed to display a single question from a database.  However, it currently displays all the questions (and notes and category).  The problem is I cant work out how to get it to display just the qid I am querying.

This should display phpquestion.php?qid=2 but it just displays all the question as does phpquestion.php?qid=7 etc

Code: [Select]
ini_set('display_errors', 1);
error_reporting(-1);


$query = "SELECT * FROM questions";

if(isset($_GET['question']))
{
$question = $_GET['question'];
$query .= " WHEN qid is '$qid' LIMIT 0, 1";
}

$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))

{

$question = $row['question'];
$notes = $row['notes'];
$category = $row['category'];
$qid = $row['qid'];

echo "$question |
$notes |
$category </br> ";
}

if ($_GET['qid'] == $qid ) {
echo 'Sorry, this question is not available.  Please visit our <a href="http://www.ukhomefurniture.co.uk">Homepage</a>.';
}

function sanitizeString($string)
{
return mysql_real_escape_string($string);
}

$question = sanitizeString($_GET['qid']);
$query .= " WHERE question is '%$qid%'";

// close connection
mysql_close();



?>

Hi all,
I am currently storing dates and times for certain events in the following format:

$now= date('Y-m-d H:i:s');

This variable is then stored in a MySQL field of type DATETIME. I am looking for a way to query the database to return all results in a certain month, or within a certain time range, or a certain year etc.

How would this be achieved?

e.g. If I wanted to select everything for the year 2009, would it be:
SELECT * FROM table WHERE date BETWEEN '2009-01-01 00:00:00' AND '2009-12-31 11:59:59' ?

Thanks!

SELECT COUNT( City ) AS Hotel, City, CountryName, Country
FROM `activepropertylist`
WHERE MATCH(city,Countryname) AGAINST ('South shields*' IN BOOLEAN MODE)
GROUP BY City,Countryname,Country
ORDER BY City
LIMIT 15

hi all I have a query against the database table which uses the full text index's and I am limiting this to 15 rows for the ajax   problem is it is returning Africa and other things before south shields   is there something I am missing as I know South shields is in there when scrolling through all the results     here is my query    

Hi,

I'm very new to PHP and have managed to get through most of the problems I've encountered so far, but I have got to one point that I can't seem to solve.

I have two mysql tables (Categories & Products), I have a page that shows the categories (generated from the categories table) and when a category is selected it lists the products in that category.

Due to stock etc. not all categories always have products associated to them.

The Category & Product Tables are automatically updated, so what I need to do is only show categories that have products associated to them, both tables have a column category_no, and nothing I've tried so far seems to work.

The code I have so far is:-

Code: [Select]
Require_once('config.php');

Mysql_connect(db_host, db_user, db_password);

@mysql_select_db(db_database) or die( "unable to select database");

$query="select * from categories";

$result=mysql_query($query);

$num=mysql_numrows($result);

Mysql_close();
Thanks

Pete

Here is where I'm starting:

Code: [Select]

SELECT * FROM wp_usermeta WHERE meta_key = 'wp_1_s2member_custom_fields' AND meta_value LIKE '%3%'


As the PHP produces the page, I'm going to have 15 different options coming from the Code: [Select]
meta_value LIKE  part.  I'm assuming I can set up my 15 different DIVs, each with their own IF  statement involving the meta_value. 

Right?  I'm not going to have to have 15 different queries, am I?


I assume it would start like this:
Code: [Select]
$query = 'SELECT * FROM wp_usermeta WHERE meta_key = "wp_1_s2member_custom_fields"';
$results = mysql_query($query);
while($line = mysql_fetch_assoc($results))

But from there, I don't know if it's possible to deal with partial data with IF statements.

Hi guys,

I need help in my code,

it states 'Error querying database' when I set
$result = mysql_query($query, $dbc).

And strange thing happens when I switched $query and $dbc place, and I  got a different error msg
$result = mysql_query($dbc, $query).
'Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in D:\inetpub\vhosts\championtutor.com\httpdocs\report.php on line 31
Error querying database'


Any idea what is happening? Below is my code
Lastly, do you guys have any debug tool software to recommend, so that it can assist me in debugging my code. Thanks


<?php
  $first_name = $_POST['firstname'];
  $last_name = $_POST['lastname'];
  $when_it_happened = $_POST['whenithappened'];
  $how_long = $_POST['howlong'];
  $how_many = $_POST['howmany'];
  $alien_description = $_POST['aliendescription'];
  $what_they_did = $_POST['whattheydid'];
  $fang_spotted = $_POST['fangspotted'];
  $email = $_POST['email'];
  $other = $_POST['other'];
 
  $dbc = mysql_connect('*******', '*******', '*******', '*******')
    or die('Error connecting to MySQL server.');

  $query = "INSERT INTO aliens_abduction (first_name, last_name, when_it_happened, how_long, " .
    "how_many, alien_description, what_they_did, fang_spotted, other, email) " .
    "VALUES ('$first_name', '$last_name', '$when_it_happened', '$how_long', '$how_many', " .
    "'$alien_description', '$what_they_did', '$fang_spotted', '$other', '$email')";

  $result = mysql_query($query, $dbc)
    or die('Error querying database.');

  mysql_close($dbc);

  echo 'Thanks for submitting the form.<br />';
  echo 'You were abducted ' . $when_it_happened;
  echo ' and were gone for ' . $how_long . '<br />';
  echo 'Number of aliens: ' . $how_many . '<br />';
  echo 'Describe them: ' . $alien_description . '<br />';
  echo 'The aliens did this: ' . $what_they_did . '<br />';
  echo 'Was Fang there? ' . $fang_spotted . '<br />';
  echo 'Other comments: ' . $other . '<br />';
  echo 'Your email address is ' . $email;
?>

<?php
include 'core/classes/Query.php';
include 'core/functions/recaptchalib.php';
	
$sqlQ = "SELECT * FROM `servers`";
$qResult = $database->query($sqlQ);
$i = 0;
while ($row = $qResult->fetch_assoc()) {
$query = new Query($row['address'], $row['query_port'], $row['engine']);
$info  = $query->query();

if(!$info) {	
	$info = $query->return_false();
}
	
	$details = array(
			'players' => $info['players'],
			'details' => $info['details']
		);
	$details = json_encode($details);
		

	$update = $database->prepare("UPDATE `servers` SET `status` = ?, `online_players` = ?, `maximum_online_players` = ?, `map` = ?, `details` = ?, `cachetime` = unix_timestamp() WHERE `server_id` = {$row['server_id']}"
	$update->bind_param('sssss', $query->status, $info['general']['online_players']['value'], $info['general']['maximum_online_players']['value'], $info['general']['map']['value'], $details)
	$update->execute(); }
?>

		

What I'm trying to accomplish is query every server & update every one of the rows for each server listed.  This does nothing ( its a cronjob , as it has to run every 5 minutes , newbie so not entirely sure if this is event the right approach )

I have three tables: events, orderdetails & orders.

First I query orderdetails to find all the records that match the EventID:

$query1 = SELECT * FROM orderdetails WHERE EventID = $_SESSION['EventID'];

This returns 4 records.  These 4 records have a field called DetailOrderID which is the foreign key for orders.OrderID.  Next I need to query the results of the first query to find all the records in the orders table that match up.  For example:

SELECT * from orders where $query1.DetailOrderID = orders.OrderID.

How would I go about doing this?  I'm head down the temporary table solution but wanted to through this one out for discussion before I invest too much time.

Hello,

I'm attempting to write a PHP script to query WMI for an IP address of a host. The reason I'm doing this instead of getbyhostname is because NAT translations on the network can cause inaccurate results. The question is two-fold:

1) My Script is as follows
<?

$obj = new COM ( 'winmgmts://localhost/root/CIMV2' );
$wmi_network = $obj->ExecQuery("Select * From Win32_NetworkAdapterConfiguration");

foreach ( $wmi_network as $wmi_call )
{
    $ipaddr = $wmi_call->IPAddress;
    echo $ipaddr;
}
?>


I get the following error when running the script:
Quote

Catchable fatal error: Object of class variant could not be converted to string



2) How do I go about querying a remote host on the network with valid credentials?


Thank you

I've been trying to fix this piece of script so i can query the results from a database.

What i want to do is to display the results from the database like below:

Product Heading
price
      Subproduct - $price

Each item would have a check box next to them. I have managed to display the items but not the prices. I've looked over the code several times but i'm lost on what i should do.

Anyway here's the code, i hope someone here can view it and let me know what i'm doing wrong or what i'm not doing.

<?php
       $get_cats = "SELECT * FROM sub_service WHERE industry='$industry'";
       $run_get = mysql_query($get_cats) or die(mysql_error());
       $tmp = array();
       $x=1;
       while($rw = mysql_fetch_assoc($run_get)){
if (!array_key_exists($rw['service'],$tmp)) {
         $tmp[$rw['service']] = array();
}
$tmp[$rw['service']][] = $rw['sub_service'];
}

foreach ($tmp as $service => $items) {
?>
       <div id="industry_wrapper">
             <h2><?php echo $service ?></h2>
                                        </div>
                                        <div id="select_all_holder">
                                            <div id="select_all_input">
                                                <input type="checkbox" class="toggleElement" name="toggle" onchange="toggleStatus()" />
                                            </div>
                                            <div id="select_all_txt">
                                                <p>Select All Services - $</p>
                                            </div>
                                        </div>
                                        <?php
echo'
                                        <div class="service_holder">
                                            <table width="650" cellpadding="0" cellspacing="5">
                                        ';
foreach ($items as $cat) {
?>
                                                <tr>
                                                    <td width="28" align="center"><input type="checkbox"  /></td>
                                                    <td width="605"><p><?php echo $cat ?> - $<?php echo $tmp['price']; ?></p></td>
                                                </tr>
                                            <?php
}
echo'</table></div>';
}
?>

I have a table in a mysql database with 5 columns, id, Name, Wifi, Bluetooth, GPS, with rows that are for example 1, Galaxy S2, yes, yes, yes. So basically i want to build a form that has check boxes (3 checkboxes for wifi bluetooth and GPS respectively) that once selected will query the database depending on which check boxes are selected. I have made the form but need to know what to put in the filter.php to make the results be displayed accordingly. Ideally if anyone knows how i would want it so the results were shown on the same page which i believe u need to use ajax to happen but any help on how to show the results will be greatful.

the code for the form i have made is as follows:
Code: [Select]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>test</title>
</head>
<body>

<form action="filter.php" method="post">
<INPUT TYPE=CHECKBOX NAME="option[]" VALUE="Wifi" id="r1">Wifi
<INPUT TYPE=CHECKBOX NAME="option[]" VALUE="Bluetooth" id="b1">Bluetooth
<INPUT TYPE=CHECKBOX NAME="option[]" VALUE="GPS" id="g1">GPS
<input type="submit" name="formSubmit" value="Submit" />

</form>

</body>
</html>
im not sure on how to make the filter.php page but i do have this code for displaying the all the data but i want to kno how to make it only display the data from the choices on the checkboxes

Code: [Select]
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>pls work</title>
    </head>
    <body>
<?php
    function h($s) {
        echo htmlspecialchars($s);
    }
    mysql_connect("localhost", "root", "") or die (mysql_error());
    mysql_select_db("project") or die (mysql_error());
    $result= mysql_query('SELECT * FROM test') or die('Error, query failed');
?>
 
<?php if (mysql_num_rows($result)==0) { ?>
    Database is empty <br/>
<?php } else { ?>
    <table>
        <tr>
            <th></th>
            <th>Name</th>
            <th>Wifi</th>
            <th>Bluetooth</th>
            <th>GPS</th>
        </tr>
        <?php while ($row= mysql_fetch_assoc($result)) { ?>
            <tr>
                <td>
                    <a href="uploaded-images/<?php h($row['Name']); ?>.jpg">
                        <img src="uploaded-images/<?php h($row['Name']); ?>.jpg" alt="test"/>
                    </a>
                </td>
                <td><a href="textonly.html"><?php h($row['Name']); ?></a></td>
                <td><?php h($row['Wifi']); ?></td>
                <td><?php h($row['Bluetooth']); ?></td>
                <td><?php h($row['GPS']); ?></td>
            </tr>
        <?php } ?>
    </table>
<?php  } ?>
    </body>
</html>

hi 

I have 2 tables (members & friendship)



Members table columns:
--------------------------------------------
username | password |    email    | etc ....
--------------------------------------------
Person 1     xxxxxx        x@x.com
person 2     yyyyyy        y@y.com
 
Friendship columns:

---------------------------------------
username |   friend   |  status  |
---------------------------------------
person 1     person 2     friends
person 2     person 1     friends
person 1     person 3     pending



I want to query the friendship table: "SELECT friend FROM friendship WHERE username ='$_SESSION['myusername'];'  AND status='friends' "

(so its basically getting the user names of anyone who is the logged in users friend)

And then use the returned user names  to select their data from the members table.

Any help much appreciated!

^.^
 

Ok, I need some serious help here.

I have a jQuery .load method that retrieves a single users name from a database. However... I also need to incorporate error checking into the script that the .load method access, so that, if the user enters a name in the field that is not in the database, it will insert it into the database, and then then pull it back out again to be returned by the ajax call.

This is the jQuery that performs the call:
Code: [Select]
$('#submit').click(function(e) {
var user = $('#user').val();
var checkurl = "indexData.php";
$('#username').load(checkurl, 'user=' + user);
e.preventDefault();
});

This is the script form indexData.php:
Code: [Select]
<?php
  $db = mysql_pconnect('localhost', 'joeme0', 'Galatians2');
if (!$db) 
{
   die('Could not connect: ' . mysql_error());
}
mysql_select_db('joeme0_brave');
$name = $_POST['name'];
$checkQuery = "select name from users where name='$name'";
$checkResult = mysql_query($checkQuery);
if ($checkResult > 1)
{
$insQuery = "insert into users (id, name) values ("", '$name')";
$insResult = mysql_query($insQuery);
if ($insResul  0)
{

$query="select name from users where name='$name'";
$result=mysql_query($query);
while($row=mysql_fetch_array($result)) 
{ 
echo $row['name'];
}
}
else
{
echo "Cold not insert your name into the database";
}
}
else
{
$query="select name from users where name='$name'";
$result=mysql_query($query);
while($row=mysql_fetch_array($result)) 
{ 
echo $row['name'];
}
}
?>


So far I can get it to pull out a name that is manually entered into the database no problem. But I can't get it to insert a name that isn't in the database, and then query the database a second time to pull that same name back out again.

The page is pretty simple. The user comes to the page, enters their name in the text field, either clicks the submit button or presses the enter key. The scripts fire off the ajax call to the database, check to see if the name exists, if not enter it and pull it back out (this is where I have the problem), if so, pull it out and display it back on the page.

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=334481.0