SQL Issue [Basic?]

[Nitr0]

mySQL Version- 5.0.81-community

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /public_html/header.php on line 85

Could not run query: Unknown column 'used' in 'where clause'

 

The Header:

<?
$select=mysql_query("SELECT * FROM auths WHERE email='".$_GET['email']."' and done=0");
if (mysql_num_rows($select) >=1 )
{
?>
<tr>
<td colspan=3 align=center>
<b><a href=redeem.php?email=<?=$_GET['email']?>>You have pins waiting to be redeemed, click here</a></b>
<br><br>
</td></tr>
<?}?>
<tr>
<td width=33% align=center>
<?
$pins2 = mysql_query('SELECT * from pins WHERE used=0');
if (!$pins2) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
$rows2 = mysql_num_rows($pins2);
?>

 

No idea what the issue is. Any comments/help will be appreciated

 

 

[trq]

The error is pretty clear. You reference the column 'used' in your query, this column doesn't exist.

[trq]

Your first query is also failing for some reason. You should check $select is a resource before trying to use it.

[Nitr0]

Here is the entire mysql db that I import.. Could you perhaps tell me what exactly I need to do/fix?

-- phpMyAdmin SQL Dump
-- version 2.11.9.4
-- http://www.phpmyadmin.net
--
-- Host: 173.201.136.99
-- Generation Time: Aug 01, 2010 at 08:35 AM
-- Server version: 5.0.91
-- PHP Version: 5.2.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `a7916366_pinshop`
--

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

--
-- Table structure for table `auths`
--

CREATE TABLE `auths` (
  `auth` varchar(255) NOT NULL,
  `pin1` bigint(19) NOT NULL,
  `pin2` bigint(19) NOT NULL,
  `pin3` bigint(19) NOT NULL,
  `pin4` bigint(19) NOT NULL,
  `pin5` bigint(19) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `auths`
--

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

--
-- Table structure for table `info`
--

CREATE TABLE `info` (
  `id` int(11) NOT NULL default '1',
  `admin_email` varchar(255) NOT NULL default '',
  `admin_msn` varchar(255) NOT NULL default '',
  `admin_sythe` varchar(255) NOT NULL default '',
  `admin_password` varchar(255) NOT NULL default '',
  `pin_price` decimal(11,2) NOT NULL default '3.00',
  `pins_sold` int(11) NOT NULL default '0',
  `title` varchar(255) NOT NULL default 'Automated Pin Shop',
  `slogan` varchar(255) NOT NULL default ' Get your pins!'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `info`
--

INSERT INTO `info` VALUES(1, 'xxxx@hotmail.com', 'xxxxx2@hotmail.com', 'user1', 'password1', 3.00, 0, 'Automated Pin Shop', 'Get your pins!');

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

--
-- Table structure for table `pins`
--

CREATE TABLE `pins` (
  `id` int(11) NOT NULL auto_increment,
  `pin` bigint(19) NOT NULL,
  `country` varchar(255) NOT NULL,
  `type` enum('SMS','PBP') NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `pins`
--

[trq]

Indeed. There is no column called 'used' within the pins table.

[trq]

ps: There is also no column called 'done' in your 'auth' table. That would be why that query fails as well.

[Nitr0]

Any idea on what to do to fix this?

 

I was given this entire script from a friend, and it was at one point functioning, so I am assuming the database needs some modifications?

 

Thanks

[PFMaBiSmAd]

tell me what exactly I need to do

 

You need to define what you want your code to do and what your table structures are before you write any of your code or your queries.

 

As thorpe has already pointed out by looking at your code and your table definitions, your auths table does not contain a column named done and your pins table does not contain a column named used. In fact, none of your tables contain columns by those two names.

 

Since we don't know what you intended your code to do and don't have the slightest idea what column names you did want to use in those two queries, you, the programmer writing this code, are the only one here who knows what the queries should be referencing and would be the only one here who knows if you intended those tables to have those columns in them or if you intended to use column names that already exist in those tables.

[Nitr0]

The pins are 8 digit numeric pins which are redeemed by the users. The code can only be used once, thus the "used" colummn. This will not exist right away as no pins will be used at first, as all will be new. Any ideas?

[trq]

I was given this entire script from a friend,

 

I would contact that friend.

[Nitr0]

I have no contact with him anymore. Since I am "illiterate" when it comes to PHP/mySQL, I assume I will go about this paying someone. Could you give me a rough estimate as to how much you think it would cost to hire someone to fix this?

[trq]

Could you give me a rough estimate as to how much you think it would cost to hire someone to fix this?

 

Not based on the information provided here.

 

Have you tried simply adding those fields and seeing if the code knows what to do with them?

 

Otherwise, yeah, your probably going to need to hire someone who knows what they are doing. We have a freelance board for such requests.

[Nitr0]

I have no idea how to add the fields..

[trq]

Do you have access to phpmyadmin? Its pretty self explanatory within there.

[Nitr0]

Yes I do.

[trq]

So login and try and add the columns. I don't use it so can't provide instructions, but, like I said, it's all pretty self explanatory.