?
Solved

insert query not working on server only on localhost

Posted on 2010-09-20
8
Medium Priority
?
339 Views
Last Modified: 2012-05-10
Hi,

I have a insert query that does not seem to work.

 

I am creating a entry into matches to get match_id and then use match_id to populate the rest of the info into the other table.

 

It works on localhost, but not on my server. It gives me this:
Warning:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/site/create-fixtures.php on line 39

line 39 is: while($match = mysql_fetch_assoc($result))
$season_id = date('Y');

 

// Get the list of matches
$result = mysql_query('SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id');
while($match = mysql_fetch_assoc($result))
{
  // Create a match record and 2 matchscore records
  mysql_query("insert into matches set season_id = $season_id");
  $match_id = mysql_insert_id();
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['hometeam']}");
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['awayteam']}");
}

Open in new window

0
Comment
Question by:cataleptic_state
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 

Expert Comment

by:orsobruno
ID: 33714613
try to correct the query

SELECT h.team_id as hometeam, a.team_id as awayteam FROM team AS h, team AS a WHERE h.team_id <> a.team_id
0
 
LVL 2

Expert Comment

by:rameshfromind
ID: 33714852
I think this query will return the null value thats why getting error if there is any value available only control enter the while loop.

1. There is no data in server database thourgh the condition[h.team_id <> a.team_id].
2. But you have  the data in local server.

'SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id'

If you need any assistance let me know.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33716058
Hi,
the team information is in the server database.

Also on some of the other pages it says "No database selected"
0
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

 
LVL 4

Expert Comment

by:chrisbloom7
ID: 33724490
When you are running locally, are you also connecting to a local database? And then when you run it on the remote server, is the database local to the webserver? And are you updating your config file or database connection values to use the proper credentials in each environment?

You could also check to see if either the connection string is wrong once you push the code to the remote server, or the database doesn't have the proper permissions to allow connections from the remote server.
0
 
LVL 5

Expert Comment

by:innotionent
ID: 33724703
Is that all your code?
where is your mysql conection command?


$link = @mysql_connect("localhost", "username", "password") or die("<b>Could not connect to DB</b>");
	
$myquerystring = "select * from table where foo = bar";
	
if(!($result = mysql_db_query("database",$myquerystring,$link))){
	print("MySQL reports: " . mysql_error() . "\n");
		exit();
}

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33724945
Hi,
This is all I have on this page (in the way of mysql):

<?php require_once('Connections/db.php'); ?>
<?php
 include "signInChk.php";
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$season_id = date('Y');

// Get the list of matches
$result = mysql_query('SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id');
while($match = mysql_fetch_assoc($result))
{
  // Create a match record and 2 matchscore records
  mysql_query("insert into matches set season_id = $season_id");
  $match_id = mysql_insert_id();
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['hometeam']}");
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['awayteam']}");
}


$maxRows_news = 6;
$pageNum_news = 0;
if (isset($_GET['pageNum_news'])) {
  $pageNum_news = $_GET['pageNum_news'];
}
$startRow_news = $pageNum_news * $maxRows_news;

mysql_select_db($database_db, $db);
$query_news = "SELECT * FROM news ORDER BY news_id DESC";
$query_limit_news = sprintf("%s LIMIT %d, %d", $query_news, $startRow_news, $maxRows_news);
$news = mysql_query($query_limit_news, $db) or die(mysql_error());
$row_news = mysql_fetch_assoc($news);

if (isset($_GET['totalRows_news'])) {
  $totalRows_news = $_GET['totalRows_news'];
} else {
  $all_news = mysql_query($query_news);
  $totalRows_news = mysql_num_rows($all_news);
}
$totalPages_news = ceil($totalRows_news/$maxRows_news)-1;

mysql_select_db($database_db, $db);
$query_enable_login = "SELECT disable_login FROM season";
$enable_login = mysql_query($query_enable_login, $db) or die(mysql_error());
$row_enable_login = mysql_fetch_assoc($enable_login);
$totalRows_enable_login = mysql_num_rows($enable_login);

// Initialize the Alternate Color counter
$ac_sw1 = 0;
?>

Open in new window

0
 
LVL 5

Accepted Solution

by:
innotionent earned 2000 total points
ID: 33725034
i see you have the database variables in the code. but where are the actual values in your script?

that no database selected error you are getting means exactly that.

you haven't specified a database for it to query.

$database_db and $db must have values in them somewhere. just make sure that you do.
0
 
LVL 4

Expert Comment

by:chrisbloom7
ID: 33725499
Somewhere in Connections/db.php there should be a line that calls mysql_connect(). If not, then that is part of your problem. If it's there, then you need to make sure you are not getting a connection error remove any "@" sign before the call and refresh the page, then check for any errors displayed on the page or in your PHP error log. If the connection is failing any error message should give you an indication of why. If you feel that you need to post your Connections/db.php file be sure to redact any sensitive information (e.g. username, password, database URL) first.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question