DB Error: no such field ???

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
zanderzz
Forum Newbie
Posts: 7
Joined: Thu Oct 23, 2008 3:48 pm

DB Error: no such field ???

Post by zanderzz »

Hi,


I"m getting this error "DB Error: no such field" Then I click on a transactions link on my site.

The mysql DB seems to have the transactiosn tables in it.

How do I start fixing this?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: DB Error: no such field ???

Post by jaoudestudios »

Post the code that is part of this and post your database schema.
zanderzz
Forum Newbie
Posts: 7
Joined: Thu Oct 23, 2008 3:48 pm

Re: DB Error: no such field ???

Post by zanderzz »

jaoudestudios wrote:Post the code that is part of this and post your database schema.

Here's the code

Code: Select all

 
<?php
/* Filename:
*   Author:
*
*   Copyright 2004 TL and SM consulting
*   All Rights Reserved
*/
include_once("include/include.php");
if(!isset($_SESSION['username'])) {
    ob_start();
    exit(header("Location: index.php"));
    ob_end_flush();
} else {
if(WIN == true) {
    chdir(EPRIME_DIR);
}
include_once("classes/models/LoginModel.php");
include_once("classes/passive-active/MessageModel.php");
include_once("include/pager.php");
 
if(isset($_REQUEST['logout'])) {
        unset($_SESSION['username']);
        setcookie("eprimeuser", "", time() - ((3600 * 24) * 2));
        setcookie("eprimehash", "", time() - ((3600 * 24) * 2));
        session_destroy();
}
 
    
    $user = $_SESSION['username'];
 
 
// Retrieve transactions for seller
$bt= "select * from user_transactions,user_transactions_price".
       " inner join user_transactions_items on user_transactions.transactionid = user_transactions_items.item_id".
       " where user_transactions.transactionid = user_transactions_price.item_id and".
       " buyer=? and buyer_show='yes' order by timestamp desc limit 100";
$btdata =& $db->query($bt,$user);
        if (DB::isError($btdata)) {
            die($btdata->getMessage());
        } 
//
 
 
 
// Retrieve transactions for buyer
$st = "select * from user_transactions,user_transactions_price".
       " inner join user_transactions_items on user_transactions.transactionid = user_transactions_items.item_id".
       " where user_transactions.transactionid = user_transactions_price.item_id and".
       " seller=? and seller_show='yes' order by timestamp desc limit 100";
 
$stdata =& $db->query($st,$user);
        if (DB::isError($stdata)) {
            die($stdata->getMessage());
        } 
 
 
        
 
    include_once(INDEX_FILES."index_site.php");
 
}
 
 
?>
 
 
 
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: DB Error: no such field ???

Post by jaoudestudios »

Can you post your database schema.

Also which line is the error pointing too?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: DB Error: no such field ???

Post by califdon »

zanderzz wrote:Hi,

I"m getting this error "DB Error: no such field" Then I click on a transactions link on my site.

The mysql DB seems to have the transactiosn tables in it.

How do I start fixing this?
That error is pretty specific. One of those tables does not have a field that was named in your SQL. It could be a misspelling or just a mistake in naming.
zanderzz
Forum Newbie
Posts: 7
Joined: Thu Oct 23, 2008 3:48 pm

Re: DB Error: no such field ???

Post by zanderzz »

hey ,

which part do you want exactly? you want teh whole mysql file? ... there's data in that.

here's what it looks like in sqlAdmin

Table Action RecordsTip Type Collation Size Overhead
admins Browse Structure Search Insert Empty Drop 3 MyISAM latin1_swedish_ci 3.1 KiB -
cookies Browse Structure Search Insert Empty Drop 1 MyISAM latin1_swedish_ci 1.1 KiB 68 B
messages Browse Structure Search Insert Empty Drop 40 MyISAM latin1_swedish_ci 4.1 KiB -
remote_info Browse Structure Search Insert Empty Drop 0 MyISAM latin1_swedish_ci 1.0 KiB -
simplecount Browse Structure Search Insert Empty Drop 1 MyISAM latin1_swedish_ci 1.0 KiB -
trans Browse Structure Search Insert Empty Drop 4 MyISAM latin1_swedish_ci 3.3 KiB -
users Browse Structure Search Insert Empty Drop 39 MyISAM latin1_swedish_ci 4.2 KiB -
user_account_settings Browse Structure Search Insert Empty Drop 44 MyISAM latin1_swedish_ci 3.4 KiB -
user_balance Browse Structure Search Insert Empty Drop 23 MyISAM latin1_swedish_ci 2.7 KiB -
user_details Browse Structure Search Insert Empty Drop 39 MyISAM latin1_swedish_ci 7.5 KiB -
user_messages Browse Structure Search Insert Empty Drop 371 MyISAM latin1_swedish_ci 165.1 KiB -
user_messages_reply Browse Structure Search Insert Empty Drop 306 MyISAM latin1_swedish_ci 70.9 KiB -
user_messages_sent Browse Structure Search Insert Empty Drop 423 MyISAM latin1_swedish_ci 106.7 KiB -
user_payments Browse Structure Search Insert Empty Drop 20 MyISAM latin1_swedish_ci 3.4 KiB -
user_payments_data Browse Structure Search Insert Empty Drop 20 MyISAM latin1_swedish_ci 2.8 KiB -
user_transactions Browse Structure Search Insert Empty Drop 29 MyISAM latin1_swedish_ci 10.9 KiB -
user_transactions_cancelled Browse Structure Search Insert Empty Drop 2 MyISAM latin1_swedish_ci 1.1 KiB -
user_transactions_items Browse Structure Search Insert Empty Drop 29 MyISAM latin1_swedish_ci 3.7 KiB -
user_transactions_price Browse Structure Search Insert Empty Drop 29 MyISAM latin1_swedish_ci 3.1 KiB -
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: DB Error: no such field ???

Post by califdon »

No, no, no. Database schema means to list the names of the tables and their columns (fields). Like this:

Code: Select all

[b]table1[/b]:
   ID       Int(10)  (PK)
   Lname    varchar(24)
   Fname    varchar(24)
   DOB      Date
   JobID    Int(10)  (FK)
 
[b]table2[/b]:
   ID       Int(10)  (PK)
   Job      varchar(64)
   Rate     double(8,2)
etc.
zanderzz
Forum Newbie
Posts: 7
Joined: Thu Oct 23, 2008 3:48 pm

Re: DB Error: no such field ???

Post by zanderzz »

Where would I find that?

I need a step by step... the guy who programmed all this for me passed away like a year ago.


I guess this would not be it either.

Code: Select all

 
 
--
-- Table structure for table `user_transactions`
--
 
CREATE TABLE `user_transactions` (
  `transactionid` varchar(100) NOT NULL default '0',
  `buyer` varchar(100) NOT NULL default '',
  `seller` varchar(100) NOT NULL default '',
  `status` varchar(100) NOT NULL default '',
  `buyer_accept` varchar(100) NOT NULL default 'no',
  `buyer_paid` varchar(100) NOT NULL default 'no',
  `last_update` varchar(100) NOT NULL default '',
  `started` varchar(100) NOT NULL default '',
  `contract` longtext NOT NULL,
  `inspect_end` varchar(100) NOT NULL default '',
  `inspect_start` varchar(100) NOT NULL default '',
  `inspection` varchar(100) NOT NULL default '',
  `fee_fee` varchar(100) NOT NULL default '',
  `cancelled` char(3) NOT NULL default 'no',
  `seller_show` varchar(10) NOT NULL default 'yes',
  `buyer_show` varchar(10) NOT NULL default 'yes',
  `buyer_approve` char(3) NOT NULL default 'no',
  `timestamp` timestamp(14) NOT NULL,
  `description` longtext NOT NULL,
  UNIQUE KEY `transid` (`transactionid`),
  FULLTEXT KEY `full` (`transactionid`),
  FULLTEXT KEY `seller` (`seller`),
  FULLTEXT KEY `buyer` (`buyer`),
  FULLTEXT KEY `fulltextidsearch` (`transactionid`)
) TYPE=MyISAM;
 
 
 
 
 
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: DB Error: no such field ???

Post by califdon »

That will do just fine for one of the tables, user_transactions. Can you find the same thing for the other two tables (user_transactions_price and user_transactions_items)? The point is to determine what all the field names are and compare them with 2 queries in the code you provided earlier, in order to find which field is mis-named or missing. In your code, look for these 2 blocks of lines:

Code: Select all

// Retrieve transactions for seller
$bt= "select * from user_transactions,user_transactions_price".
       " inner join user_transactions_items on user_transactions.[color=#008000]transactionid[/color] = user_transactions_items.[color=#008000]item_id[/color]".
       " where user_transactions.[color=#008000]transactionid[/color] = user_transactions_price.[color=#008000]item_id[/color] and".
       " [color=#008000]buyer[/color]=? and [color=#008000]buyer_show[/color]='yes' order by [color=#008000]timestamp[/color] desc limit 100";

Code: Select all

// Retrieve transactions for buyer
$st = "select * from user_transactions,user_transactions_price".
       " inner join user_transactions_items on user_transactions.[color=#008000]transactionid[/color] = user_transactions_items.[color=#008000]item_id[/color]".
       " where user_transactions.[color=#008000]transactionid[/color] = user_transactions_price.[color=#008000]item_id[/color] and".
       " [color=#008000]seller[/color]=? and [color=#008000]seller_show[/color]='yes' order by [color=#008000]timestamp[/color] desc limit 100";
Somewhere you're going to find that at least one of the queries (the code that begins with "select * from ...") will call for a field name that doesn't exist in your tables. That's what that error was telling you.

I have highlighted the field names above in green. Where a field name is preceded by another name and a period, that's the name of the table in which that field should be found.

A couple of comments about the code (even though it does not bear on your problem):
The lines that begin with // are comments (ignored by the program, but useful to humans). For the two blocks of code above, the comments are swapped; that is, where it says: "// Retrieve transactions for seller", the code that follows is actually for the buyer, and vice versa for the other one! It doesn't affect the operation of the code, but it might be very confusing to anyone trying to read and understand it. Also, in each of those 2 queries, there is code like: buyer=? and seller=?. I'm not sure what the developer meant, but that's invalid syntax, I believe. If he is searching for a question mark in that column, the question mark should have single quotes around it, just as the word 'yes' has, nearby.
Last edited by califdon on Sat Oct 25, 2008 6:56 pm, edited 1 time in total.
Reason: Add comments
zanderzz
Forum Newbie
Posts: 7
Joined: Thu Oct 23, 2008 3:48 pm

Re: DB Error: no such field ???

Post by zanderzz »

Hey, sorry for the late reply. Here are schemes you asked for. I'm not too sure of what you want me to do exactly.


--
-- Table structure for table `user_transactions_price`
--

DROP TABLE IF EXISTS user_transactions_price;
CREATE TABLE user_transactions_price (
item_id varchar(100) NOT NULL default '',
item_price varchar(100) NOT NULL default '',
item_shipping varchar(100) NOT NULL default '',
buyer_pay_amount varchar(50) NOT NULL default '',
seller_pay_amount varchar(50) NOT NULL default '',
item_total varchar(100) NOT NULL default '',
UNIQUE KEY itemid (item_id)
) TYPE=MyISAM;

--
-- Dumping data for table `user_transactions_price`
--


.......................................................................................and

--
-- Table structure for table `user_transactions_items`
--

DROP TABLE IF EXISTS user_transactions_items;
CREATE TABLE user_transactions_items (
item_title varchar(100) NOT NULL default '',
item_id varchar(100) NOT NULL default '',
item_quantity varchar(100) NOT NULL default '',
UNIQUE KEY itemid (item_id),
FULLTEXT KEY itemtitle (item_title)
) TYPE=MyISAM;

--
-- Dumping data for table `user_transactions_items`
--
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: DB Error: no such field ???

Post by califdon »

OK, the problem seems to be those 2 places in the queries that I questioned earlier: buyer=? and seller=?. As you can see by looking through your table structures, there is no such field named either buyer or seller, which is precisely what the error message was saying. Even if there were, the syntax would still be incorrect. So the problem is, I have no idea what the programmer had in mind, so there's no way I can help you fix it. That's tough, that your programmer died (tough on him, too, no doubt), but you'll have to find somebody to examine your entire operation and determine what needs to be done. But that's the part that generated your error. It's puzzling that you're just now having this problem. The code you showed could never have worked, as far as I can see.
zanderzz
Forum Newbie
Posts: 7
Joined: Thu Oct 23, 2008 3:48 pm

Re: DB Error: no such field ???

Post by zanderzz »

I'm lost too. The program worked, it uses phrame and PHP4... then they upgrade something... with some help I fixed the errors it's just the Database now.

I noticed the buyer and seller in...

Table structure for table `user_transactions`


# `buyer` varchar(100) NOT NULL default '',
# `seller` varchar(100) NOT NULL default '',

I donno. I'm not a programmer. :(
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: DB Error: no such field ???

Post by califdon »

zanderzz wrote:I'm lost too. The program worked, it uses phrame and PHP4... then they upgrade something... with some help I fixed the errors it's just the Database now.

I noticed the buyer and seller in...

Table structure for table `user_transactions`


# `buyer` varchar(100) NOT NULL default '',
# `seller` varchar(100) NOT NULL default '',

I donno. I'm not a programmer. :(
Oh! You're absolutely correct! Somehow, I missed that, guess I was looking at the other 2 tables. OK, so that's not a mistake, but the question mark following the = sign is still not correct. It would have to be either a number or a text value enclosed in quote marks, as you see the other values in those statements, like 'yes'. I can only guess that someone wasn't sure what to put in there and left a question mark, but never returned to correct it. I'm sorry to say this, but your problem really needs an experienced PHP programmer to examine it and fix it for you.
Post Reply