PHP - Querying The Same Table Twice

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

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!

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;
?>

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.

<?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 )

Is there a way to query some information depending on if a table field DOES NOT include a specific value? I've got a table that contains over 100 locations. I want to organize the data on the frontend by country (specifically US and International). How would I go about filtering out locations that DO NOT belong in USA?

$q = "SELECT * FROM table WHERE COUNTRY='USA'";

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

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>

This topic has been moved to MySQL Help.

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

Hey there...

I'm so stuck on this problem I was hoping someone could help...

I need to query a MySQL Database using array values...

Here is my array ... $query_keywords = Array ( [7] => business [10] => home [11] => depo )
 
Here is my php ...$query = "SELECT * FROM product WHERE product_name, product_comment IN ($query_keywords)";
$result=mysql_query($query);

if(mysql_num_rows($result) > 0)
{
   echo "results";
}
else
{
   echo "no results";
}


Unfortunately, I get this ...
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in blah\blah\results.php on line 99

Please help me
All comments greatly appreciated...

I created a simple search box which will query my table and match the input value to one of my columns. two of these columns store comma separated values. if i query a column other than a column which stores my csv i can see my search results. if i query a column which stores my csv i will not see results unless the search value matches the first value within the column. how would i be able to get say the second or third or forth value. here is the code i am using to query the table any help would be appreciated thanks.

Code: [Select]
$q = $this->db->query("SELECT * FROM table WHERE col1 LIKE 'searchvalue'".
             " OR col2 LIKE 'searchvalue'".
             " OR col3 LIKE 'searchvalue'".
             " OR col4 LIKE 'searchvalue'".
             " OR FIND_IN_SET('searchvalue', col5) > 0 ".
             " OR FIND_IN_SET('searchvalue', col6) > 0");

It's actually not precisely PHP-related... but if we want the most complete (worldwide) results, which Whois server should we send our PHP-based Whois query to? (ARIN, for instance, just catalogs the US...)

Thanks for your suggestions! )

--Dave