PHP - Query Problem

Hello everybody. I have a query-question...

I have a form that writes data into the database. BUT... I want to verify that data if it allready exists. There are 3 fields that needs to be checked:

1. Type; <- if yes, check 2.
2. Coordinates1 (+ or - 2 from existing record) <- if yes check 3. If no: insert record and echo: "Mine posted succesfully in the database!"
3. Coordinates2 (+ or - 2 from existing record) <- if yes echo: "There is allready a mine of this type in the database on that location!". If no: insert record and echo: "Mine posted succesfully in the database!"

(One coordinate uses this format: xxxxxx,yyy (Where xxxxxx is coordinates1 and yyy is coordinates2).

A comparison for 1 variable I can find, but not for three and also not how to incorporate the + or - 2.
Any help is greatly appreciated. 

Christiaan

code I have so far:
Code: [Select]
<?
include 'mining_cfg.php';

// html collect variable

$type = $_POST['type'];
$level = $_POST['level'];
$coordinates1 = $_POST['coordinates1'];
$coordinates2 = $_POST['coordinates2'];

if ($type == ".$type") AND if ($coordinates1 == ".$coordinates1") AND if ($coordinates2 == ".$coordinates2"){
echo "Mine posted succesfully!";
} else {
echo "There is allready a mine of this type in the database on that location!";
}

// sql insert into database 
$sql ="INSERT INTO mining(type, level, coordinates1, coordinates2)
VALUES ('".$type."', '".$level."', '".$coordinates1."', '".$coordinates2."')";

//uitvoeren van de query :
if (!($temp = mysql_query($sql,$connection)))
showerror();
 
header ( "Location: http://swr.infiniteserve.com/swr-site/test/post_mining.php" );

?>

  <form method="post" >
 <fieldset>
 
  <table>
  <tr>
  <td>naam van het gerecht:</td>
  <td><input type="text" name="nieuwgerecht"  /></td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td>ingredient 1:<input type="text" name="ingredient1" /></td>
  </tr>
  <tr>
   <td></td>
  <td></td>
  <td>ingredient 2:<input type="text" name="ingredient2" /></td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td>ingredient 3:<input type="text" name="ingredient3" /></td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td>ingredient 4:<input type="text" name="ingredient4" /></td>
  </tr>
  <tr>
  <td></td>
  <td></td>
  <td>ingredient 5:<input type="text" name="ingredient5" /></td>
  </tr>
  </table>
  <input type="submit" name="cmdvoegtoe" value="voeg toe"  />
  </fieldset>
  </form>
  <?php
  if(isset($_POST['cmdvoegtoe']))
  {
    if (empty($_POST['ingredient1']))
    {
       echo "<script>alert(\"ingredient nummer 1 moet zeker ingevuld zijn!\");</script>";
    }
    else
    {
      $query="INSERT INTO tblgerecht VALUES(NULL, '$_POST[nieuwgerecht]')";
      
      mysql_query($query, $connectie) or die('Error, insert query failed');
      

    }
  }
  ?>



Parse error: parse error in ... on line 118
problem with mysql query

can someone help me with it

I have a running total that I am pulling from a database that I need to sort by year. I have tried adding WHERE YEAR(datepicker) = '2015' but keep getting an error. Here is the original query that works fine when I run it. Just don't need all the records.   SELECT id, datepicker, startmiles, endmiles, O.totalMiles, car, section, (select sum(totalMiles) FROM vehicle WHERE id <= O.id) 'runningTotal' FROM vehicle O ORDER BY datepicker DESC

Hi guys,

Im just after a little help here because i cant seem to get my script to work correctly. Basically i have a table full of data and i want to be able to extract a months worth of data but split that data into weeks from within the month.

This is my database structu
Code: [Select]
-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 13, 2011 at 09:13 AM
-- Server version: 5.5.16
-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `time_man`
--

-- --------------------------------------------------------

--
-- Table structure for table `clock_status`
--

CREATE TABLE IF NOT EXISTS `clock_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(24) NOT NULL DEFAULT '0',
  `location` int(4) NOT NULL DEFAULT '0',
  `date_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `amend_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `hours_worked` varchar(25) NOT NULL DEFAULT '',
  `lunch` int(11) NOT NULL DEFAULT '0',
  `break` int(11) NOT NULL DEFAULT '0',
  `sick` char(3) NOT NULL DEFAULT 'NO',
  `holiday` char(3) NOT NULL DEFAULT 'NO',
  `comments` text NOT NULL,
  `auth` tinyint(1) NOT NULL DEFAULT '0',
  `auth_date` date NOT NULL,
  `paid` tinyint(1) NOT NULL DEFAULT '0',
  `pay_rate` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=734 ;

--
-- Dumping data for table `clock_status`
--

INSERT INTO `clock_status` (`id`, `user_id`, `location`, `date_time`, `status`, `amend_time`, `hours_worked`, `lunch`, `break`, `sick`, `holiday`, `comments`, `auth`, `auth_date`, `paid`, `pay_rate`) VALUES
(720, '653467435', 3301, '2011-10-12 04:00:00', 0, '2011-10-12 06:35:00', '9300', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(721, '653467435', 3301, '2011-10-12 06:30:00', 0, '2011-10-12 11:25:00', '17700', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(722, '5030341511007', 3301, '2011-10-13 17:45:00', 0, '2011-10-30 22:10:00', '15900', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'H'),
(723, '653467435', 3301, '2011-10-13 01:15:00', 0, '2011-10-13 07:15:00', '21600', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(724, '653467435', 3301, '2011-10-13 03:15:00', 0, '2011-10-13 05:15:00', '7200', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(725, '653467435', 3301, '2011-11-11 05:45:00', 0, '2011-11-11 13:20:00', '27300', 0, 0, 'NO', 'NO', '', 1, '2011-11-11', 0, 'L'),
(726, '653467435', 3301, '2011-11-11 01:15:00', 0, '2011-11-11 08:25:00', '3663', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'L'),
(727, '12345678954565456', 3301, '2011-11-11 05:45:00', 0, '2011-11-11 16:25:00', '3662', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'L'),
(728, '50226900', 3301, '2011-11-21 09:30:00', 0, '2011-11-21 17:00:00', '27000', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'H'),
(729, '5030341511007', 3301, '2011-11-22 09:30:00', 0, '2011-11-11 15:30:00', '21600', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'H'),
(730, '5030341511007', 3301, '2011-11-22 06:00:00', 0, '2011-11-17 09:20:00', '12000', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'H'),
(731, '653467435', 3301, '2011-11-22 06:45:00', 0, '2011-11-22 12:30:00', '20700', 0, 0, 'NO', 'NO', '', 1, '0000-00-00', 0, 'L'),
(732, '5030341511007', 3301, '2011-11-22 07:45:00', 0, '2011-11-22 15:15:00', '3661', 0, 0, 'NO', 'NO', '', 1, '0000-00-00', 0, 'H'),
(733, '653467435', 3301, '2011-11-22 04:45:00', 0, '2011-11-22 14:05:00', '33600', 0, 0, 'NO', 'NO', '', 1, '0000-00-00', 0, 'L');

-- --------------------------------------------------------


--
-- Table structure for table `staff`
--

CREATE TABLE IF NOT EXISTS `staff` (
  `person_id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL DEFAULT '',
  `staff_number` varchar(20) NOT NULL DEFAULT '0',
  `dob` date NOT NULL DEFAULT '0000-00-00',
  `pay_rate` char(1) NOT NULL DEFAULT '',
  `date_amended` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `expiry` text NOT NULL,
  PRIMARY KEY (`person_id`),
  UNIQUE KEY `staff_number` (`staff_number`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;

--
-- Dumping data for table `staff`
--

INSERT INTO `staff` (`person_id`, `name`, `staff_number`, `dob`, `pay_rate`, `date_amended`, `expiry`) VALUES
(16, 'Person Two', '3456', '1990-06-23', 'H', '2011-06-23 07:40:38', ''),
(17, 'Person Three', '5678', '1986-05-29', 'H', '2011-01-24 12:18:03', ''),
(15, 'Person One', '1234', '1995-11-29', 'L', '2011-01-18 16:59:53', ''),
(47, 'Person four', '50226900', '1980-04-23', 'H', '2011-06-17 10:50:50', ''),
(48, 'Person Two', '653467435', '1980-01-13', 'L', '0000-00-00 00:00:00', '01/02/1982'),
(50, 'Person One', '5030341511007', '1983-07-13', 'H', '0000-00-00 00:00:00', ''),
(52, 'Person Three', '12345678954565456', '2000-02-01', 'L', '0000-00-00 00:00:00', '01/03/2019');

-- --------------------------------------------------------


/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
This is the script so far...

Code: [Select]
<?php require_once("../includes/connection.php"); ?>
<?php 
$date1 = '2011-10-28';
$date2 = '2011-11-27'; 
$ident = '3301';

$sql10 = "SELECT WEEK(date_time) as Week, staff.name, clock_status.user_id, sum( clock_status.hours_worked ) AS sum_hours, clock_status.hours_worked, clock_status.amend_time
FROM clock_status, staff
WHERE amend_time >= '$date1' AND amend_time < '$date2' AND clock_status.auth =1
AND clock_status.user_id = staff.staff_number
AND clock_status.location = '3301'
GROUP BY user_id
ORDER BY Week, user_id";  
$query10 = mysql_query($sql10) or die(mysql_error());

?>
<table style="border: 1 solid black;">
<tr>
<th>Staff No</th>
<th>Name</th>
<th>Sum hours</th>
<th>Week 1</th>
<th>Week 2</th>
<th>Week 3</th>
<th>Week 4</th>
<th>Week 5</th>
</tr>
<?php while ($row = mysql_fetch_assoc($query10)) { ?>
<tr>
<td><?php echo $row['user_id'] ;?></td>
<td><?php echo $row['name'] ;?></td>
<td><?php echo $row['sum_hours'] ;?></td>
<td>Week 1 hours here</td>
<td>Week 2 hours here</td>
<td>Week 3 hours here</td>
<td>Week 4 hours here</td>
<td>Week 5 hours here</td>
</tr>
<?php } ?>
</table>
<?php require("../includes/footer.php"); ?>The table should list each person once, with the total hours, then the amount of hours in each week in a different column. As represented in my table.

If someone could help me on this issue i would be eternally grateful...


thanks in advance.

Senseis,

I give nothing error. But mysql query dont put in to the table. I am using variables...


$ara = '/Public Game - /'; 
$degistir = '';
$seo_var=preg_replace( $ara, $degistir, $yeni_title );
$ara2 = '/\s+/'; 
$degistir2 = '';
$seo_var2=preg_replace( $ara2, $degistir2, $seo_var );
$seo_link = tr_converter($seo_var);
$makalequery = "INSERT INTO `jos_content` (`id`, `title`) VALUES (NULL, '$seo_link')";
$makaleekle = mysql_query($makalequery) or die("Problem with the query: $q<br>" . mysql_error());


Thank you for help...

Note: I'm dealing with this problem for three days..
Best Regards...

Hope someone can help. i am trying to search between two date ranges but am not sure how to add it to the rest of my query.

this is my original query which works fine .. it does not have the start and end date part
Code: [Select]
$query = "SELECT * FROM table WHERE hdt1 like '%$hdt1_search%' and hdt2 like '%$hdt2_search%' and hdt3 like '%$hdt3_search%' and dateadd like '$dateadd_search'";
this is the start date to end date search, ...it works ok when it is the only thing in the query
Code: [Select]
$query = "SELECT * FROM table WHERE dateadd >= '$start_date' AND dateadd <= '$end_date'";



BUT, when i try to put it all together the only two fields that get search are the  start date to end date search. ...

Code: [Select]
$query = "SELECT * FROM table WHERE hdt1 like '%$hdt1_search%' and hdt2 like '%$hdt2_search%' and hdt3 like '%$hdt3_search%' and dateadd like '$dateadd_search' and  dateadd >= '$start_date' AND dateadd <= '$end_date' ";

what am i doing wrong????

Hi --

This query seems to be problematic because the UPDATE is not being performed.  Could you please take a gander and let me know what is the problem?

BTW, the table "teamy" does contain 630 records.  Thanks in advance!

$sql = "SELECT COUNT( * ) AS records FROM teamy" ;
$result = mysql_query( $sql ) ;
if ( ! $result )
{ die ( __line__ . "_teamy_" . mysql_error() ) ; }

$a_count = mysql_fetch_assoc($result);
if ( $a_count['records'] = 0 )
{
   echo "No records found in teamy." ;
}
else
{ 
   /*** Update r_rost_rma ***/
   $sql = "UPDATE r_rost_rma 
             JOIN teamy
               ON r_rost_rma.student_id = teamy.student_id
              SET r_rost_rma.teachername = teamy.team
            WHERE RTRIM( UPPER( r_rost_rma.localcourse ) ) = 'HOMEROOM'" ;

   $result = mysql_query ( $sql ) ;
   if ( ! $result )
   { die ( __line__ . "_update_r_rost_rma_" . mysql_error() ) ; }
}

I have a table 'combo' with two fields, id and char. I want to populate a table, with the following query which fails. $sql = mysql_query("SELECT id,char FROM combo ORDER BY id DESC LIMIT 28");  I a bit rusty on sql but can't see what is wrong with this. Can anyone help please.

Hi all i bet this is going to be obvious, but I ran out of ideas.
I have the following script but it's not inserting. I gave the user  database specific insert update and select rights but something is not working.
maybe someone could help me out.


$dbc = mysqli_connect('localhost','slave','password','mydatabase')
        or die ('error connecting to mysql server');

//display stuff
$query = "SELECT * FROM mydatabase";
$result = mysqli_query($dbc, $query);

while($row =  mysqli_fetch_array($result))
    {
        echo 'id: '.$row['ID'].
    ' and firstname is: '.$row['firstname'].
    ' and lastname is: '.$row['lastname'].'<br />';
    }
//insert stuff

$query = "INSERT INTO `mydatabase`.`users` (`firstname`, `lastname`) VALUES ('john', 'doe')";
$result = mysqli_query($dbc, $query);


mysqli_close($dbc);


-edit ok i think i fixed it, i tried the root user and it worked. gave the slave user more rights. apperently besides database specific i ahve to give the slave user global insert update and select rights too. Can someone confirm that?

Hi all,
I have a form for users to input posts into their blog. This works fine with the INSERT INTO query in my code. However, when I test it and check the database the entry is going in fine but there is no id attached to it. I'm not really sure how to use an INNER JOIN with an INSERT. As you will see, I have tried to work around it, but I am getting a parameter error "mysqli_fetch_array() expects parameter 1 to be mysqli_result, object given". I suspect this is because the query is not working. I have echoed out the SESSION['name'] variable $whichuser and it works fine. Any help appreciated.
Code: [Select]
<?php
session_start(); // always on the top of the script when user should be logged in
 if(isset($_SESSION['loggedin'])){
 }else{
header("location: index.php");
 }
require('connection.php');

$whichuser=$_SESSION['name'];

if(empty($_POST['title'])){
$title=NULL;
echo "<b>Warning:</b>You haven't given your post a title.</br>";
}else{
$title=mysqli_real_escape_string($connection, trim($_POST['title']));
}
if(empty($_POST['content'])){
$content=NULL;
echo "<b>Warning:</b>You haven't entered any content.</br>";
}else{ 
$content=mysqli_real_escape_string($connection, trim($_POST['content']));
}
if(($title) && ($content)){
$newentry = "INSERT INTO blogposts (title, content, timeofpost) 
VALUES ('$title', '$content', NOW())";
if($newentry){
$join= "SELECT * FROM blogposts INNER JOIN users ON blogposts.post_number = users.username WHERE username='$whichuser'";
if($join){
$entry=mysqli_query($connection, $join) or die(mysqli_error($connection));
}else{
echo "THERE'S A PROBLEM WITH THE QUERY.";
}

while($newpost = mysqli_fetch_array($connection, $entry)){
extract($newpost);
echo "<h3>$title</h3></br>";
echo "$content</br>";
echo "$whichuser</br>";
}
}
}
?>

How can I get this query to work? I am having problems with the HAVING section of it.


Code: [Select]
SELECT id,price, ( 6371 * acos( cos( radians($lat) ) * cos( radians( lat ) )
  * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) )
  * sin( radians( lat ) ) ) ) AS distance
FROM fuelstations
HAVING distance < 250 , price > 0
ORDER BY price LIMIT 0 , 60;

This topic has been moved to MySQL Help.

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

What I am trying to do is query a database using a loop conditional
and return the data but my code only repeats the first instance multiple times!

My code is below, I have tried to use comments to explain what should be happeing!

$rows = null;
$q1 = "SELECT * FROM table";
$r1 = mysql_query($q1) or die(mysql_error());

$i = 1;
while($i <= 5){

$start = (1000 * $i);
$end = ($start + 1000);

while($a1 = mysql_fetch_array($r1, MYSQL_ASSOC)){
/* query the database where 'start' is  >= 1000
 and where 'end' is <= 2000, this should loop so the next time is
 where 'start' >= 1000 * $i should be 1000 then 2000, 3000 etc...
 and where 'end' should be 2000, 3000, 4000 etc...
*/
if($a1['start'] >= $start && $a1['end'] <= $end){
$rows .= $a1['col1'].':'.$a1['col2']."\n";
}

}
echo nl2br(trim($rows.'<p>'));
$i++;
}

/* The problem is that it always shows multiple instances of the first result only 
where start = 1000 and end = 2000
*/
Any help would be much appreciated!! 

Hi,

i'm trying to select some entries from my DB, i don't quite understand what's the problem here, i'm using this:

Code: [Select]
//Customers Online
$query = "SELECT COUNT(*) as Anzahl FROM customers WHERE status = 1";
$queryerg = mysql_query($query) OR die(mysql_error());
while($row = mysql_fetch_array($queryerg)){
  $customers_online = $row[0];
}
Which works fine, now i want to select by country and i'm doing this:

Code: [Select]
//Customers DE
   $query = "SELECT COUNT(*) as Anzahl FROM customers WHERE country = 'de'";
   $queryerg = mysql_query($query) OR die(mysql_error());
   while($row = mysql_fetch_array($queryerg)){
     $customers_de = $row[0];
   }
Which doesn't work? If i print / echo $customers_de i just get a blank value, not even 0 or anything just blank.

Any help ?

Hi there
I am using a query to find what user made a comment. The problem I am having is I get the wrong user from the query. Everything seems fine in the database and I just can't seem to track the problem

$query = "SELECT * FROM messages";

$result=mysql_query($query) or die("Invalid Query : ".mysql_error());

while ($row = mysql_fetch_assoc($result)){

$pid=($row["pid"]);

$id=($row["id"]);

$query = "SELECT * FROM users WHERE id='$pid'";

$rresult=mysql_query($query) or die("Invalid Query : ".mysql_error());

while ($row = mysql_fetch_assoc($rresult)){

$user=($row["user"]);

}
                       }


The query is working in reverse I.E when I search for what messages are from the user. Can anyone see my problem.