PHP - Need Help With A Querying A Partial Column...

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 want to show part of the text from a page containing my article on my main page. The article resides in a database in mysql. How do I limit the amount of text that is shown without setting up a separate excerpt box for the article. I want to then provide a link so the user can go to another page that displays the entire article. Thanks for any suggestions.

I've run into a little bit of a logistical nightmare on some registration pages I've taken over work on.  On these pages, parents register their kids for classes. The pages have been coded as such:
page one: the user enters name and personal info, and selects one or two classes to register for, on submit, they go to page two.
page two: their info is entered into the mysql database's registration table on a unique id  The user verifies the total, enters a discount and submits
page three: credit card info is added to registration table, and sent to authorize.net.
page four: payment processed, and they enter the data for their kids into the attendees table
page five: confirmation and done.

My issue is that, at first I saw people entering page one data, going to page two, then for some reason, hitting the back button. They could then enter the data again. I'd have two entries in the registration table for the same person. I was going to put some sort of unique key on the name and an email, but then I saw scenario two...

Another person enters data and registers one kid.. then for whatever personal reason goes back and registers a second kid in a completely separate transaction.

So I can't put that key on there, but is there a way to prevent them from going back and reentering twice. I don't want to have to blow up the code to do it at the end of everything. and that would take implementing sessions, wouldn't it? I'm not so versed at that. Any thoughts?

Hello,

I wrote a script where i can enter a stock symbol and it get saved in my database. This works, but when i enter the stock symbol i would like to also get the current stock price get saved into the database.

The link for the stock price (i.e. AAPL): http://quote.yahoo.com/d/quotes.csv?s=AAPL&f=l1&e=.csv

How do i programm this in the script below?

Besides above, notice that "AAPL" in the link above has to be a varaible, which changes where i for example type in "GS".

Form:
Quote

<html>
 <body>
   <form action="portfolio/insert_buy.php" method="post">
      Symbol: <input type="text" name="symbol"><br />
      <input type="submit" value=" Submit">
   </form>
 </body>
</html>


PHP-script
Quote

<?php
   $conn = mysql_connect("xxx","xxx","xxx");
   $db      = mysql_select_db("xxx",$conn);

$sql="INSERT INTO portfolio (symbol)
VALUES
('$_POST[symbol]')";

if (!mysql_query($sql,$conn))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($conn);
?>



I hope someone can help me out, thanks!

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

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

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

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

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.

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!

I have a array of file path strings and trying to search the array for a string that would be the end of the path sting and return the index of the file path that contains the string. I've tried using something like this, but it finds the keyword in anyplace in the file path. I've also tried strripos() as well with the same results. Any suggestions?

function array_search_partial($arr, $keyword) {
    foreach ($arr as $index => $string) {
			if(strpos($string, $keyword) !== FALSE) {
				return $index;
			}
    }
}

 

Hello.

My query results are only pulling partial values for table entries with a space (Example:  AJ Smith only returns AJ and not the full name ).

I am sure this is an easy fix and I am just missing something.

Thank you.