PHP - Error In Querying Database

Hey all. I'm using this sort of thing to query with my application:
Code: [Select]
        try
        {
            $stmt = $this->db->prepare($sql);
            /* Bind parameter if id was passed, ensure it's of integer type */
            if(!empty($id))
            {
                $stmt->bindParam(":id", $id, PDO::PARAM_INT);
            }
            $stmt->execute();
            $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $stmt->closeCursor();

            return $results;           
        }
        catch(Exception $e)
        {
            die($e->getMessage());
        }which has been working out great, with the only annoyance that sometimes, I need to run a query and I know I'm supposed to get only one row. However, I have to access associated elements via $results[0]['fieldName']. How do I run a query to return exactly one result so I can just use $results['fieldName']?

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 everyone. I'm a query very similar to the one below with no success. Unfortunately SQLs and joins are not my strong suit.

Code: [Select]
SELECT f.field1, f.field2, f.field3
FROM fields_table f
WHERE f.field1 = SOME_VALUE
  LEFT JOIN fields_table s
  ON s.field2 > f.field2
  AND s.field3 <= f.field3
    LIMIT 1
I would like the first SELECT query to be returned even if there are no matches on the join, also I'd like the second table query to be limited to 1. Is this possible? Could anyone revise the table above to make this work?

Thanks!

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

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

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!

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.

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.

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'";

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

This topic has been moved to MySQL Help.

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

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.

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>